Pau Lo
asked on
truncation error on import of csv file into access.
I am having a strange issue trying to an import a *.csv file into a Microsoft access database table for analysis. The data is actually a csv representation of a complete windows 7 security.evtx which from within windows 7 event viewer application I have selected to 'save all events as', and then selected evtx. When I open the csv file in Microsoft excel, it loads up fine, where one row of data represents one event from the event log. However when I then try and import that data into Microsoft access it goes horrendously wrong, and it seems to split the data into approximately 22 rows for a single event. In the 'import text wizard' in access (2010), when I select the csv file, it initially defaults to 'fixed width', and the preview already shows the mess of an import that is due to follow. If I manually select delimited it does not amend itself back to one row of data per event. So my query is what can I do to make the import more efficient, and how come excel can handle the data in a 1 row per event format but access cannot?
To add to my misery after the import goes horrendously wrong, I did also spot the dreaded importerrors table, of which there are 94813 Field Truncation errors on a field called keywords. No idea why though, as keywords is a fairly simple field which will have basic entries such as 'Audit Success'. It actually appears to be the 6th field in the data which may be the problem, that has lots of text, e.g. a sample value I found had over 1000 characters with lots of blank lines between the sentences/descriptions.
Any ideas on tips to get this data to import 'properly'?
To add to my misery after the import goes horrendously wrong, I did also spot the dreaded importerrors table, of which there are 94813 Field Truncation errors on a field called keywords. No idea why though, as keywords is a fairly simple field which will have basic entries such as 'Audit Success'. It actually appears to be the 6th field in the data which may be the problem, that has lots of text, e.g. a sample value I found had over 1000 characters with lots of blank lines between the sentences/descriptions.
Any ideas on tips to get this data to import 'properly'?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Jim - I forced it to try through delimited the delimiter is comma separated but I also tried tab to see how it would behave and its the same problem. I even went into 'advanced' and tried to skip every other field but the 1st field in the data and it is still pulling through data from what I presume is the troublesome 6th column. Essentially none of the delimiters in the wizard import the data properly...
ASKER
@John - regarding am I using the import functionality in access - yes I was.
ASKER
It actually imports fine into SQL Server using the 'import flat file' option under tasks.
ASKER
I played around with every option possible and it turns out it was because the text qualifier was defaulting to {none} and when I made it " it imports every row with error! No idea why it defaulted to {none} but such a simple thing in the end.
Post a small file if you can please.
I don't have a win 7 station here to test with. Format probably hasn't changed, but best to play safe and get the one your having a problem with.
Also note that it's possible to get very specific with Access by using a schema.ini file, which tells the wizard exactly how the file is formatted.
The default wizard can only do so much and guesses because it only samples the first ten (think that's right - might be six - would have to look it up) lines of the file.
Jim.
I don't have a win 7 station here to test with. Format probably hasn't changed, but best to play safe and get the one your having a problem with.
Also note that it's possible to get very specific with Access by using a schema.ini file, which tells the wizard exactly how the file is formatted.
The default wizard can only do so much and guesses because it only samples the first ten (think that's right - might be six - would have to look it up) lines of the file.
Jim.
<<I played around with every option possible and it turns out it was because the text qualifier was defaulting to {none} and when I made it " it imports every row with error! No idea why it defaulted to {none} but such a simple thing in the end>>
There you go.
Jim.
There you go.
Jim.
<<No idea why it defaulted to {none} but such a simple thing in the end.>>
That's because CSV files don't always need text delimiters and that's from WAY back when.
Keep in mind, Access started out over 20 years ago. Back then, 1200 baud modems were the norm. Every character counted and CSV's didn't always use text delimiters.
The default has always been none.
Jim.
That's because CSV files don't always need text delimiters and that's from WAY back when.
Keep in mind, Access started out over 20 years ago. Back then, 1200 baud modems were the norm. Every character counted and CSV's didn't always use text delimiters.
The default has always been none.
Jim.
I was always having a time with import/export specifications until someone told me about the "MSysIMEXSpecs" and "MSysIMEXColumns" tables which held the data for the specifications. (You don't have these until you create your first specification.) You can edit specs with these and even transfer specs from one db to another by simply copying the records from one to the other. (Make sure you handle the autonumber fields properly.)
Now since they are MSys tables, you need to be really careful that you don't screw them up, but with a little understanding and practice, it makes handling specifications a lot easier.
One last thing.... I have seen developers spend a lot of time trying to get a ".csv" file imported properly when it wasn't a properly formatted ".csv" file to begin with (that followed the rules for .csv format.) No amount of messing with the specs can fix that. The first thing anyone needs to do is make sure their .csv file is properly formatted so it can be read according to the .csv formatting and specification rules. That's why so many experts want to see a sample of the file before trying to figure out what's wrong. Since you said it worked properly elsewhere, that gave us hope that it was, indeed, a properly formatted file to begin with.
Import-Export-Specifications-Tables.PNG
Main-Specification-Fields.PNG
Now since they are MSys tables, you need to be really careful that you don't screw them up, but with a little understanding and practice, it makes handling specifications a lot easier.
One last thing.... I have seen developers spend a lot of time trying to get a ".csv" file imported properly when it wasn't a properly formatted ".csv" file to begin with (that followed the rules for .csv format.) No amount of messing with the specs can fix that. The first thing anyone needs to do is make sure their .csv file is properly formatted so it can be read according to the .csv formatting and specification rules. That's why so many experts want to see a sample of the file before trying to figure out what's wrong. Since you said it worked properly elsewhere, that gave us hope that it was, indeed, a properly formatted file to begin with.
Import-Export-Specifications-Tables.PNG
Main-Specification-Fields.PNG
Well this is great when it comes to properly formatted .csv (or similar txt based files)
The import does give you the option to define the delimiter to separate the fields but even this might not be enough because there numerous cases where there are two spaces when there should be only one ....and a lot of others..
To add to the problem there are also cases where the fields starts with another type (unless you explicitly defined) .e.g it is Numeric...so Access thinks is a number...great...but later on we have text....so error...
The options 2:
1. Really inspect the import process....check each and every fieldss...treat them as text...unless you are 100% that there is not a chance for an error...
2. The hard but with complete control....here we loose the "easy" and we are swiming on our own...we open the file...iterate it line by line and we make our splitting.
For this you need VBA