Spreadsheet upload data convertion error

[is49460]

Hello everyone!

I got excel spreadsheet with lets say 50000 lines that I need to upload into access db. I created vba code to perform automated upload into specified table, however, as far as i understand, when access performs upload it takes first 25 rows of data from excel and determines the type of each field, then performs upload and then converts the data to match the one specified in the table. The problem I'm facing is that I have field "invoice number" that can be ranging from 8 to 11 digits. during upload time access recognizes the field as int, but when it comes accross 11 digit invoice number it blanks it out since its out of range for int's.
Is there a way to specify what data type to use before upload? (besides sorting data in excel)
Thanks a lot for any help.

[MMcCarthy]

Hello everyone!

I got excel spreadsheet with lets say 50000 lines that I need to upload into access db. I created vba code to perform automated upload into specified table, however, as far as i understand, when access performs upload it takes first 25 rows of data from excel and determines the type of each field, then performs upload and then converts the data to match the one specified in the table. The problem I'm facing is that I have field "invoice number" that can be ranging from 8 to 11 digits. during upload time access recognizes the field as int, but when it comes accross 11 digit invoice number it blanks it out since its out of range for int's.
Is there a way to specify what data type to use before upload? (besides sorting data in excel)
Thanks a lot for any help.

You have two options.

1. Create the table first and upload the data into an existing table.
2. Change the excel file to a csv file and create an import specification on how you want the data imported.

Mary

[is49460]

I'm trying to pursue the first suggested solution but some values are still getting lost during the conversion. for example:
excel file:
invoice number
1234
1235
1236
...

line 250: 1234df6

well, access drops line 250 because based on first 25 records it determined the field to be an integer, even tho I specify it as text in the destanation table... so it takes it as an integer uploads it and then converts in to text as specified in the table and that the problem I'm trying to work around.
second option is not convinient for users.
Thanks for replying...
any other advises how to upload data from excel?

[MMcCarthy]

I'm trying to pursue the first suggested solution but some values are still getting lost during the conversion. for example:
excel file:
invoice number
1234
1235
1236
...

line 250: 1234df6

well, access drops line 250 because based on first 25 records it determined the field to be an integer, even tho I specify it as text in the destanation table... so it takes it as an integer uploads it and then converts in to text as specified in the table and that the problem I'm trying to work around.
second option is not convinient for users.
Thanks for replying...
any other advises how to upload data from excel?

If you created the table then just go in and change the number format to Long Integer or text whichever suits.

[is49460]

I surely did that but seems that access looks at the assigned data type only after upload is completed.

Seems like access goes through the following steps during upload process:
1. Access takes first 25 records (as a sample) to determines the data type on its own, regardless whats specified in destanation table.
2. Uploads all records (loosing the once that dont correspond with #1, even tho they DO match the specified type)
3. assign specified data type

If you have extra couple of minutes create table in access with 1 field of text data type. create excel file with same heading as in access followed by 30 rows of integers (1..30) and then some string in 31st row.
If you perform import that last cell with string in it will get dropped even tho you specified for the column to be text.
if you got couple of minutes to try that, perhaps you would understand my problem better, which I already greatly appreciate you taking your time and replying.
Thanks.

[MMcCarthy]

My apologies you are right.

I must admit I would never normally try to import a spreadsheet. Try the second option of converting it to a csv file and importing that instead.

Mary

[Denburt]

Yes, interesting indeed.

[is49460]

I'll perhaps end up writing automated macro to sort data in excel to have all strings appear up on top so access will recognize them as "text". The reason cvs file is not an option is just beacuse users get their data in excel format before they run it through DB. I want to simplify (and in the same time avoid) any data manipulations by them. Thanks for replying and if you come across any work around let me know.
Thank a lot.

[Denburt]

This is how I solved it, using code I would open the excel spreadsheet and save it as a text file. Then perform the following or something along these lines...

 Dim db As DAO.Database
    Dim DskTop
    Dim wsShell As New WshShell
    Dim wsSCut As WshShortcut
    Dim strCommandLine As String 'Command Line for shortcut to run
  Set db = CurrentDb()
DskTop = wsShell.SpecialFolders("Desktop")
   
    db.Execute _
    "SELECT * INTO NewContact FROM [text;FMT=Delimited;HDR=Yes;DATABASE=" & DskTop & ";].[Book21#txt];", _
    dbFailOnError
    db.TableDefs.Refresh

[Denburt]

Darn forgot to mention that you need to set up a schema and it should reside in the same folder. This also can be accomplished in code if you wanted to. My schema looks as such.

Schema.ini



[Book21.txt]
ColNameHeader=F alse
Format=FixedLen gth
MaxScanRows=0
CharacterSet=OE M
Col1="First Name" Char Width 10

[is49460]

This is how I solved it, using code I would open the excel spreadsheet and save it as a text file. Then perform the following or something along these lines...

 Dim db As DAO.Database
    Dim DskTop
    Dim wsShell As New WshShell
    Dim wsSCut As WshShortcut
    Dim strCommandLine As String 'Command Line for shortcut to run
  Set db = CurrentDb()
DskTop = wsShell.SpecialFolders("Desktop")
   
    db.Execute _
    "SELECT * INTO NewContact FROM [text;FMT=Delimited;HDR=Yes;DATABASE=" & DskTop & ";].[Book21#txt];", _
    dbFailOnError
    db.TableDefs.Refresh

Looks like it will be a good work around without limit to one field (the one thats sorted in my algorithm). Thanks a lot

[Denburt]

You are quite welcome!