dbTalk Databases Forums  

Losing zeros from csv during inmporting to sql server

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss Losing zeros from csv during inmporting to sql server in the microsoft.public.sqlserver.dts forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Ashish Kanoongo
 
Posts: n/a

Default Losing zeros from csv during inmporting to sql server - 07-09-2004 , 10:33 AM






Hi,

I have the foll csv file

01,12111
02,14155
05,11011
10,00114
34,00001
03,11000

I have the following code that parse the text file and and import in sql server table, it import perfectly except it is removing leading zeros so 01 is returned as 1. Why?


With Con1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
.CursorLocation = adUseServer
.Open
End With

Rst1.Open "SELECT * FROM [" & fsoFileName & "]", Con1, adOpenDynamic, adLockPessimistic

it will import all the fields properly, except remove leading zeros. Is there any idea how to handle this or I should open csv file using

Open App.Path & fsoFilename For Input As #1

and read information fields wise and record wise sequentially.

But still I would like to use "select statement".

Can anyone help me in this matter?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.717 / Virus Database: 473 - Release Date: 07/08/2004

Reply With Quote
  #2  
Old   
Duane Bozarth
 
Posts: n/a

Default Re: Losing zeros from csv during inmporting to sql server - 07-09-2004 , 10:52 AM






Ashish Kanoongo wrote:
Quote:
Part 1.1 Type: Plain Text (text/plain)
Encoding: quoted-printable
Please don't use attachments in newsgroups...

Q. Why losing leading zeroes...

A. Leading zeroes are a figment of representation, not numerically
significant...you'll have to use a String representation to retain
them--or, leave them numeric internally and use Format$("00",value) to
display them with the leading zero.


Reply With Quote
  #3  
Old   
Wayne Snyder
 
Posts: n/a

Default Re: Losing zeros from csv during inmporting to sql server - 07-09-2004 , 10:55 AM



I suspect the destination data type is numeric, and if so, the leading zeros are meaningless to the value.. You would have to convert the value to a string and add leading zeroes on output if you wish.

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote

Hi,

I have the foll csv file

01,12111
02,14155
05,11011
10,00114
34,00001
03,11000

I have the following code that parse the text file and and import in sql server table, it import perfectly except it is removing leading zeros so 01 is returned as 1. Why?


With Con1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
.CursorLocation = adUseServer
.Open
End With

Rst1.Open "SELECT * FROM [" & fsoFileName & "]", Con1, adOpenDynamic, adLockPessimistic

it will import all the fields properly, except remove leading zeros. Is there any idea how to handle this or I should open csv file using

Open App.Path & fsoFilename For Input As #1

and read information fields wise and record wise sequentially.

But still I would like to use "select statement".

Can anyone help me in this matter?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.717 / Virus Database: 473 - Release Date: 07/08/2004

Reply With Quote
  #4  
Old   
Al Reid
 
Posts: n/a

Default Re: Losing zeros from csv during inmporting to sql server - 07-09-2004 , 11:07 AM



You need a Schema.ini file to define the field data types.
See http://msdn.microsoft.com/library/de...a_ini_file.asp

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote

Hi,

I have the foll csv file

01,12111
02,14155
05,11011
10,00114
34,00001
03,11000

I have the following code that parse the text file and and import in sql server table, it import perfectly except it is removing leading zeros so 01 is returned as 1. Why?


With Con1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
.CursorLocation = adUseServer
.Open
End With

Rst1.Open "SELECT * FROM [" & fsoFileName & "]", Con1, adOpenDynamic, adLockPessimistic

it will import all the fields properly, except remove leading zeros. Is there any idea how to handle this or I should open csv file using

Open App.Path & fsoFilename For Input As #1

and read information fields wise and record wise sequentially.

But still I would like to use "select statement".

Can anyone help me in this matter?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.717 / Virus Database: 473 - Release Date: 07/08/2004

Reply With Quote
  #5  
Old   
Allan Mitchell
 
Posts: n/a

Default Re: Losing zeros from csv during inmporting to sql server - 07-09-2004 , 11:21 AM



And what is the destination datatype?

If it's an int then this will strip the 0 as seen


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Reply With Quote
  #6  
Old   
Ashish Kanoongo
 
Posts: n/a

Default Re: Losing zeros from csv during inmporting to sql server - 07-09-2004 , 11:04 PM



All the destination data type is varchar.
"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote

Hi,

I have the foll csv file

01,12111
02,14155
05,11011
10,00114
34,00001
03,11000

I have the following code that parse the text file and and import in sql server table, it import perfectly except it is removing leading zeros so 01 is returned as 1. Why?


With Con1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
.CursorLocation = adUseServer
.Open
End With

Rst1.Open "SELECT * FROM [" & fsoFileName & "]", Con1, adOpenDynamic, adLockPessimistic

it will import all the fields properly, except remove leading zeros. Is there any idea how to handle this or I should open csv file using

Open App.Path & fsoFilename For Input As #1

and read information fields wise and record wise sequentially.

But still I would like to use "select statement".

Can anyone help me in this matter?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.717 / Virus Database: 473 - Release Date: 07/08/2004

Reply With Quote
  #7  
Old   
Al Reid
 
Posts: n/a

Default Re: Losing zeros from csv during inmporting to sql server - 07-10-2004 , 08:05 AM



Your problem has to do with the text driver trying to guess at the data types. The same thing can happen when importing a csv into excel.

As I pointed out earlier in this thread, you need to create a Schema.ini file to define the data types of each field.
--

Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain


"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote

All the destination data type is varchar.
"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote

Hi,

I have the foll csv file

01,12111
02,14155
05,11011
10,00114
34,00001
03,11000

I have the following code that parse the text file and and import in sql server table, it import perfectly except it is removing leading zeros so 01 is returned as 1. Why?


With Con1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
.CursorLocation = adUseServer
.Open
End With

Rst1.Open "SELECT * FROM [" & fsoFileName & "]", Con1, adOpenDynamic, adLockPessimistic

it will import all the fields properly, except remove leading zeros. Is there any idea how to handle this or I should open csv file using

Open App.Path & fsoFilename For Input As #1

and read information fields wise and record wise sequentially.

But still I would like to use "select statement".

Can anyone help me in this matter?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.717 / Virus Database: 473 - Release Date: 07/08/2004

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.