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:
.csv file, opened in MS Excel (note - contains column headers)
.txt file, opened in the Cloud9 IDE (note - doesn't contain column headers)
.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.
dos2unixmight help, it turns line endings to UNIX standard
\nwhich Linux and MacOSX use. The opposite,
unix2dos, turns them into
\r\nused on Windows, HTTP, and other things compatible to CP/M (1970s!).