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'?