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.
11 1948 MMcCarthy 14,534
Recognized Expert Moderator MVP
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. - Create the table first and upload the data into an existing table.
- Change the excel file to a csv file and create an import specification on how you want the data imported.
Mary
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 14,534
Recognized Expert Moderator MVP
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.
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 14,534
Recognized Expert Moderator MVP
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 1,356
Recognized Expert Top Contributor
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 1,356
Recognized Expert Top Contributor
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 1,356
Recognized Expert Top Contributor
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=False
Format=FixedLength
MaxScanRows=0
CharacterSet=OEM
Col1="First Name" Char Width 10
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 1,356
Recognized Expert Top Contributor Sign in to post your reply or Sign up for a free account.
Similar topics
by: dave |
last post by:
Hello there,
I am at my wit's end ! I have used the following script succesfully to
upload an image to my web space. But what I really want to be able to do is
to update an existing record in a...
|
by: news |
last post by:
I have no idea if this is a Linux issue or PHP. Looking for clues here.
I have a PHP script which I use to upload a CSV spreadsheet into a
mySQL database. (The script follows here in a sec.)
I...
|
by: Allison Bailey |
last post by:
Hi Folks,
I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....
I would like to open an existing MS Excel spreadsheet...
|
by: Brooke |
last post by:
I am new to ASP.NET, but have been programming for about 14 years (C#
about 2 years). My manager asked me to develop a web application that would
allow employees to view a spreadsheet that is used...
|
by: Wayne Smith |
last post by:
I've come up against a major headache that I can't seem to find a solution
for but I'm sure there must be a workaround and I would really be grateful
of any help.
I'm currently building a web...
|
by: sradoff |
last post by:
I wish to use XPath to access data from within an Excel generated XML Spreadsheet (generated with MS Excel 2003). I am using an asp Web page (not .NET, not at this time). I instantiate...
|
by: ssrirao |
last post by:
There is an Excel Spreadsheet containing data, residing in an internet site.
It’s very easy to Import data from a local Excel Spreadsheet into SQL Server Database Table using DTS.
But in my case...
|
by: mix01 |
last post by:
Hi,
I am trying to get some VBA code working, but am preplex as to why it does not work. I would really appreciate any level of help.
Many thanks,
Mix01
Version of the program
|
by: KevinADC |
last post by:
Note: You may skip to the end of the article if all you want is the perl code.
Introduction
Uploading files from a local computer to a remote web server has many useful purposes, the most...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |