CSV import deletes trailing zeroes on text fields

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

CSV import deletes trailing zeroes on text fields

Simon Leo Hafner
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CSV import deletes trailing zeroes on text fields

Simon Slavin-3
On 12 Jul 2018, at 9:47am, Simon Leo Hafner <[hidden email]> wrote:

> create table foo (
>  text bar not null
> );

Should be

    bar TEXT NOT NULL

I'm not sure how your line is being parsed, but I can understand it thinking you have not set a column type.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CSV import deletes trailing zeroes on text fields

R Smith-2
In reply to this post by Simon Leo Hafner
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CSV import deletes trailing zeroes on text fields

David Burgess-2
"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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CSV import deletes trailing zeroes on text fields

Robert Weiss
 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 <[hidden email]> 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CSV import deletes trailing zeroes on text fields

David Raymond
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:[hidden email]] 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 <[hidden email]> 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users