0

I'm having problems importing a file into an sqlite database for my final project.

I have a .csv file which I want to import. I've created the table using the phplite GUI, with 14 fields (columns).

I've tried using the GUI to import the CSV file - nothing happens, and the table remains empty.

I've also tried to convert the .csv file into a .txt file (from MS excel) - hoping to be able to follow the same procedure we used to import e.g. US.txt in Pset8 (Mashup).

The first time I simply saved the .csv as a .txt file. When I tried to import the file (via the command line) I got the error "Expected 14 columns, found 1484". I interpreted this to mean that sqlite isn't recognising the end of each row in the .txt file (which does appear to have 14 separate columns, and many different rows).

After some googling, I then chose the option to save the file as a UTF-16 file (UTF-8 doesn't appear to be available from MS Excel on the Mac). However, when I imported this via the sqlite command line, I get all the rows I expected in the database - but only the first row has any data in it - the others are all empty.

It seems to me the problem is to do with the way the rows in the .csv file and/or the .txt files are terminated - and that this isn't being recognised by sqlite.

Any ideas how I can fix it?

Thanks in advance for your help.

UPDATE - thanks for the input CliffB. I've attached three screenshots of the data:

  1. .csv file, opened in MS Excel (note - contains column headers)

  2. .txt file, opened in the Cloud9 IDE (note - doesn't contain column headers)

  3. .txt file, opened in Mac's text editor (note - doesn't contain column headers)

I've also attached a screenshot of the table within my database in sqlite - which matches the columns in my .csv file

An admission - I didn't write down the exact error - so I made up the number of columns found for the purposes of the question (I knew it must be a multiple of 14). In fact the database contains 1074 rows, each with 14 columns - so 15,046 discrete data points, each representing one of 14 categories. I imagine the error actually said "Exoected 14 columns, found 15,046."

Again, I'd really appreciate your help in how to convert this into a format I can import to sqlite.

Matt

enter image description here enter image description here enter image description here enter image description here

Improve this question
4
  • Don't know what the problem is, but based on your discussion, I'd agree that it's an issue with how each line is terminated in the csv file. It looks like your file has 1011 rows. Until you find a fix, I suggest that you create a test file with about 3 to 5 rows to work with to make life easier. BTW, is there a comma after the last entry on each row? There shouldn't be. Might be helpful if you edited the question and posted the first few rows of your data file in the question.
    – Cliff B
    Commented Oct 12, 2017 at 21:01
  • That's Tab Separated Values, not Comma Separated Values (which in most cases is semicolon-separated!), you might have to specify the separator. For the line endings, dos2unix might help, it turns line endings to UNIX standard \n which Linux and MacOSX use. The opposite, unix2dos, turns them into \r\n used on Windows, HTTP, and other things compatible to CP/M (1970s!).
    – Blauelf
    Commented Oct 13, 2017 at 17:31
  • British dates might impose the next challenge on the importer :D
    – Blauelf
    Commented Oct 13, 2017 at 17:32
  • Blauelf is right, that looks like a tab separated value file. I suspect that this is causing problems. sqlite's import mechanism does not handle csv or its cousins rigorously, so there are any number of issues that will cause problems. For one, it doesn't handle quotes very well. I suggest you google "sqlite import csv" and do some research. You might have to switch to another front end. At minimum, you should consider exporting using a delimiter like a semicolon, assuming there are no semicolons in your data. If so, find something else for a delimiter.
    – Cliff B
    Commented Oct 13, 2017 at 19:32

1 Answer 1

Reset to default
0

Many thanks to @Blauelf and @CliffB for your help.

Aha! - yes, it was a tab-delimited file, even though I had downloaded it from the source website explicitly as a .csv (the source is UK government - so no surprises the IT is a bit flaky!). Anyway, I finally managed to import it by:

  1. Cleaning up the data in MS Excel (removing random carriage returns etc. within the cells).

  2. Removing all of the commas and quotation marks within the cells in MS Excel (find and replace all with either " " or just blanks, depending on what I wanted)

  3. Running dos2unix (thanks Blauelf!) on the file, to insert a newline at the end of each line.

As an aside - how would you ever know whether there is a line termination or not? In each of MS Excel, Text Editor, and the Cloud9 IDE (all on a Mac), it looks like there are 1076 separate rows - but without running dos2unix sqlite only "sees" a single row.

I now have a database with 1076 rows - hurrah!

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .