truncation error on import of csv file into access.

pma111
pma111 used Ask the Experts™
on
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'?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Ignore the field truncate errors for the moment.   Your getting that because Access thinks the records are longer then they actually are.

 You must choose the delimited format and the proper delimiters or Access won't be able to figure out the format.   My guess is that the default choices Access makes are incorrect.   Most likely the file has tabs for delimiters.    Try the wizard again and see if selecting different delimiters cleans up the display of the records.  

 If you can't figure it out, please post the file.  I don't have a Win 7 machine here to generate one.

Jim.
John TsioumprisSoftware & Systems Engineer

Commented:
I guess that you are using the build in import functionality of Access..
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

Author

Commented:
@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...
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
@John - regarding am I using the import functionality in access - yes I was.

Author

Commented:
It actually imports fine into SQL Server using the 'import flat file' option under tasks.

Author

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.
Mark EdwardsChief Technology Officer

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today