By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

How to import data in Text format in MS Access?

P: 4
Hi Experts..

I am having issues on importing full data into the database, because of failing in converting the data type.

Example as below Screenshot.

Currently I am using below codes

Expand|Select|Wrap|Line Numbers
  1. Sub ImportData()
  2. DoCmd.RunSQL "SELECT vTbl.* 
  3.               INTO NewTable 
  4.               FROM (SELECT vCSV.* 
  5.                     FROM [Text;
  6.                           FMT=Delimited;
  7.                           HDR=YES;
  8.                           IMEX=2;
  9.                           CharacterSet=437;
  10.                           DATABASE=C:\Input].[Sales.csv] As vCSV) As vTbl"
  11. End Sub
  12.  
Is there any work around for this?

thanks for your help in advance!

Attached Images
File Type: jpg Ill.jpg (10.8 KB, 217 views)
Aug 31 '18 #1

✓ answered by zmbd

Raymond,
Please keep in mind, many (if not most) companies block 3rd party images and YouTube at the company firewall. As many of us are answering questions during our spare time at work these things are blocked.

NeoPa(...)It may be that the import is interpreting the first few lines as having numerics in that position and therefore fixes the field as numeric. (...)
I think you have "hit the nail on the head"

Fortunately I'm at home today so I could see Raymond's video. By default the import uses the first 8 or so records IIRC. The first five rows shown in the Excel opening show the values as right-hand alignment which is typical with numeric data and the remaining rows as left-hand which is typical for text/string data; thus, because the first five are numeric, and these are the predominate data type in the records read when the connection is made the parser guesses that the remaining values are numeric.

I have ran into this before with some instrument data and sample names. Some of these start with numeric values and some with alpha values. The data imports correctly if the first value encountered is alpha first for the first 10 or 20 sample names; however, if numeric first for the first 10 or so samples then it pukes (sometimes with just the first sample starting with numeric values!).

I ended up using IISAM and a schema information file to parse the text. Unfortunately, my references for this are at work; however, I did find an article at Database Journal: Working with external text files in MS Access covering this method - this is quite involved or I would post the steps directly.
PLEASE DO NOT EDIT YOUR REGISTRY as shown in the first part of the article - SKIP TO THE IISAM portion of the article.
- If you make an error in the registry you can make your OS fail beyond repair and while I will empathize with your pain should that happen... - you have been warned!

One thing to note... I make one schema information file in a "working" directory, use the FileCopy() to create a copy of the orginal data file in the "working" directory followed by Name()(click on the links for syntax) to rename the working copy of the file to a common name used within the schema information file (for ISO I cannot alter the filenames of the original data). Otherwise you have to create a schema information file for each file or build each file into the schema information file - I was importing hundreds of data-files so the standard naming method saved my sanity.

Share this Question
Share on Google+
9 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,127
Raymond RS,

Welcome to Bytes!

I am unfamiliar with the Error “Null”, but my initial guess is that your invoice number needs to be a numeric value, and during the import, since the values you show that are creating the error are alpha-numeric, these values are not accepted.

We would need a more detailed description of the exact error, the structure of your tables to which you are importing your data and any other pertinent information that can help us guide you to a better solution.
Aug 31 '18 #2

P: 4
Hello Twinnyfo,

Thanks for the welcoming message. =)

Like you said, the field consist of Alpha-numeric data, that causes the problem. Following screenshot is showing the missing data from csv file and after imported to Access.



https://www.flickr.com/photos/156309.../shares/Hx2890

Thanks!
Aug 31 '18 #3

NeoPa
Expert Mod 15k+
P: 31,271
Can we see a screenshot of the error message as displayed when you run the code please.
Aug 31 '18 #4

P: 4
Hi Neopa,

The following is the video i make for the steps i went through...

https://youtu.be/ONR9phnNxOA

In fact there isn't any error messages... It just ignored on importing the data.

I understood it was because of alpha numeric data in the same field.. do you have any suggestion in overcoming this issue?
Sep 1 '18 #5

NeoPa
Expert Mod 15k+
P: 31,271
Not at this point no. Still short of any info that leads to an understanding. I'm sure the video was an attempt to help with that, but showed very little of interest.

Two points :
  1. Try including the exact contents of your CSV file in here, within the [CODE] tags.
  2. Viewing the CSV using Excel may be the default, but it's frankly very little use to anyone (Particularly to you. Not just us). It gives very little idea of the actual data there.
From what you showed in both your pictures and your video, it seems like you have very few lines of data. Unfortunately that sort of thing's hard to determine just from a picture or the video. We only see what you think to show us.

I have a suspicion of what might be going wrong, but at this stage only that. It may be that the import is interpreting the first few lines as having numerics in that position and therefore fixes the field as numeric. Thus, once it hits data that isn't numeric - it fails to import it.

Once we see the data we can look at ways to get around it. That may require you to change the data somewhat. Is that within your power? Or is the data just something you have to deal with exactly as it is?
Sep 1 '18 #6

PhilOfWalton
Expert 100+
P: 1,430
As an experiment, try reversing the order of the lines in your CSV file so that it reads the Alpha value first.

Phil
Sep 1 '18 #7

zmbd
Expert Mod 5K+
P: 5,287
Raymond,
Please keep in mind, many (if not most) companies block 3rd party images and YouTube at the company firewall. As many of us are answering questions during our spare time at work these things are blocked.

NeoPa(...)It may be that the import is interpreting the first few lines as having numerics in that position and therefore fixes the field as numeric. (...)
I think you have "hit the nail on the head"

Fortunately I'm at home today so I could see Raymond's video. By default the import uses the first 8 or so records IIRC. The first five rows shown in the Excel opening show the values as right-hand alignment which is typical with numeric data and the remaining rows as left-hand which is typical for text/string data; thus, because the first five are numeric, and these are the predominate data type in the records read when the connection is made the parser guesses that the remaining values are numeric.

I have ran into this before with some instrument data and sample names. Some of these start with numeric values and some with alpha values. The data imports correctly if the first value encountered is alpha first for the first 10 or 20 sample names; however, if numeric first for the first 10 or so samples then it pukes (sometimes with just the first sample starting with numeric values!).

I ended up using IISAM and a schema information file to parse the text. Unfortunately, my references for this are at work; however, I did find an article at Database Journal: Working with external text files in MS Access covering this method - this is quite involved or I would post the steps directly.
PLEASE DO NOT EDIT YOUR REGISTRY as shown in the first part of the article - SKIP TO THE IISAM portion of the article.
- If you make an error in the registry you can make your OS fail beyond repair and while I will empathize with your pain should that happen... - you have been warned!

One thing to note... I make one schema information file in a "working" directory, use the FileCopy() to create a copy of the orginal data file in the "working" directory followed by Name()(click on the links for syntax) to rename the working copy of the file to a common name used within the schema information file (for ISO I cannot alter the filenames of the original data). Otherwise you have to create a schema information file for each file or build each file into the schema information file - I was importing hundreds of data-files so the standard naming method saved my sanity.
Sep 1 '18 #8

P: 4
Hi ZMBD,

My apology for not being considerate. In fact, I don't really know how to attach screenshot dirct in this site, as when I click the attachment button, the dialog box was asking to provide a URL link. So I uploaded to some free site and link it here. Do you have a screenshot guides on this? Thanks! =)

With regards of the issues. Thanks for your guidance! I have resolved my problem using the schema method.

I created a Schema.ini into the same folder and run the below code in MS Access, and it works perfectly for me.

Expand|Select|Wrap|Line Numbers
  1. Sub ImportSchemaTable()
  2.     Dim db As DAO.Database
  3.     Set db = CurrentDb()
  4.     db.Execute _
  5.     "SELECT * INTO test FROM [Text;FMT=CSVDelimited;HDR=Yes;DATABASE=C:\;].[test#csv];", dbFailOnError
  6.     db.TableDefs.Refresh
  7.     RefreshDatabaseWindow
  8. End Sub
  9.  
Again Thanks for your help ZMBD.

Also, Thanks NeoPa and PhilOfWalton for offering your suggestions too! =)
Sep 2 '18 #9

P: 2
I have an issue with a fixed-width text file that I'm importing data into an existing database and I think I also got an issue with my system Asus S series laptop. I follow your instruction but still, I have an issue with it. I don't is that problem with database or System. So I contacted Asus Customer service to get Advice what happening to me.
Thanks for your guidance! I have resolved my problem using the schema method.
Sep 3 '18 #10

Post your reply

Sign in to post your reply or Sign up for a free account.