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

Ashish Kanoongo wrote:
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.

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

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

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

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

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