People double clicking .csv's to edit them in Excel has caused so many headaches.
Leading 0's dropped, things like "4E3" turned into 4000, "3-12" turned into "12-Mar", mixups between Windows encoding and UTF-8 mangling characters, etc. If you have to or prefer to view things in Excel, the way I recommend is opening a new blank session, then using the Data, Get External Data, From Text button. Then you can select the encoding, delimiter, quoting, and mark all the columns as text. Then it's "less likely" to have destroyed things. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Robert Weiss Sent: Thursday, July 12, 2018 6:01 PM To: SQLite mailing list Subject: Re: [sqlite] CSV import deletes trailing zeroes on text fields 1. Rename .csv to .txt2. Excel will now ask for column treatment on import3. Specify the problem column(s) as "text" not "general" There are other problems with csv recognizing text as numbers. I had a column with content, say 123D4. Excel recognized the old FORTRAN double precision format and called it 1230000. On Thursday, July 12, 2018, 2:39:09 PM PDT, David Burgess <dburges...@gmail.com> wrote: "CSV import deletes /leading/ zeroes on text fields" excel does this. Quite difficult to stop it from doing so. On Fri, Jul 13, 2018 at 6:52 AM, R Smith <ryansmit...@gmail.com> wrote: > I believe your subject should read: "CSV import deletes /leading/ zeroes on > text fields" - Your trailing Zero is in tact. > > And your declaration is wrong - in SQL the column name is first, then the > Type, so it must be: > CREATE TABLE foo(bar TEXT NOT NULL); > > Opening the csv file in Excel or CALC will probably do the exact same thing > - but SQLite should be better than that. > > > > On 2018/07/12 10:47 AM, Simon Leo Hafner wrote: >> >> To reproduce: >> >> create table foo ( >> text bar not null >> ); >> >> .import test.csv foo >> >> select * from foo; >> >> With test.csv: >> >> test >> 01230 >> >> Expected result: >> >> test >> 01230 >> >> Actual result: >> >> test >> 1230 >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
