How to import data in Text format in MS Access?

[Raymond RS]

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

Sub ImportData()
DoCmd.RunSQL "SELECT vTbl.* 
              INTO NewTable 
              FROM (SELECT vCSV.* 
                    FROM [Text;
                          FMT=Delimited;
                          HDR=YES;
                          IMEX=2;
                          CharacterSet=437;
                          DATABASE=C:\Input].[Sales.csv] As vCSV) As vTbl"
End Sub
 

Is there any work around for this?

thanks for your help in advance!

[twinnyfo]

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.

[Raymond RS]

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!

[NeoPa]

Can we see a screenshot of the error message as displayed when you run the code please.

[Raymond RS]

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?

[NeoPa]

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?

[PhilOfWalton]

As an experiment, try reversing the order of the lines in your CSV file so that it reads the Alpha value first.

Phil

[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.

[Raymond RS]

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.

Sub ImportSchemaTable()
    Dim db As DAO.Database
    Set db = CurrentDb()
    db.Execute _
    "SELECT * INTO test FROM [Text;FMT=CSVDelimited;HDR=Yes;DATABASE=C:\;].[test#csv];", dbFailOnError
    db.TableDefs.Refresh
    RefreshDatabaseWindow
End Sub
 

Again Thanks for your help ZMBD.

Also, Thanks NeoPa and PhilOfWalton for offering your suggestions too! =)

[asuscustomer]

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.