Speed Up Access Query to CSV Linked Tables

[Amr Ali]

I have a microsoft access database ver. 2003 with linked table to a huge csv file. This csv file is about 800 MB size what couldn't be imported to access by any way because each time I am trying to import it, it exceeds the max size of an access database. I need to make a search query into this linked table but when I am running such query it hangs and freezes because the linked tables couldn't be indexed. So, I need your advice to fix this issue and to be able to search in this csv linked table via access query or vb code.

[Rabbit]

Not much you can do. Have you tried importing the file into a blank database file and linking to that? That way you can create the proper indexes. And if space is an issue, make sure to use the smallest data types you can get away with. Other than that, the only other option is to upsize your database to a different system.

[Amr Ali]

Rabbit,
i am using all fields as text while importing the file but access doesn't let me completing the process as it shows me an error ## invalid argument coz the max size was reached.

[Mihail]

Just an idea:
Create as much databases as you need in order to import parts (not all the .csv file).
Then create a Front End database to manage all this.

Hope you understand what I mean.

[Amr Ali]

how can i control the size of csv imported data while i can't open this csv file as it's sooo huge 800 MB

[sierra7]

Hi
How many rows and columns in your csv file?

Will the csv load into Excel ? (Probably stupid question!)

S7

[Amr Ali]

it's delimited with | separator and includes 209 columns of data and about 500,000 rows

[Mihail]

http://en.wikipedia.org/wiki/Comma-separated_values

So, .csv files can be open in a normal text editor (Note Pad, Word Pad, Word). Maybe you can manage this in this mode.

Of course, if you need to do this frequently, you can forget what I say before.

Any way, you can use a text editor (I recommend Word Pad) to keep only the LAST 20 or 30 rows in order to zip it and attache to your next post (be aware: work on a copy).
I'll try to sketch some code to help you to split (or import in databases, I'll see) the .csv file.

For that I'll need some more information:
- How you want to be divided ? (Each column in order to create a table ? After a reasonable number of rows in order to create tables with the same structure ?)
- What names you wish for each column ?
- What is the max size you need ?

If you think that are other information that can help post it too.

Good luck !

[Amr Ali]

Mihail,

Thank you so much for your fast reply and for helping me, but i would like to attract your attention that i'll do it frequently on a weekly basis.
Regarding importing the csv file into word pad, i think it will be the worse choise coz i tried it before and after 2 hours it hanged and froze without success.
I attached a zip file what includes all column headers which will help you to do what you suggested before.
I need to import the csv file into more than 1 database and then manage all tables via only 1 master database or link this csv file to a database but in this case the query will be sooo slow specially in 2007 access format.

[sierra7]

OK
So your data is very 'wide' and 'long'.

I have some code that would sequentially read each row then substring the data into fields in an Access database. You could read 100,000 rows at a time but because there are over 200 columns it would be very 'code intensive' and be slow.

You would still be left with the problem of having to handle multiple databases.

I think Rabbit's original suggestion of uploading it to a different system (MS SQL Server or MySQL) then using an Access front end to manipulate it, would be a better solution.

SQL Server Express is free and downloadable.
I have just tested this by loading a 340 column wide csv into SQL Server and the Wizard handled it fine, and was quick.

Unfortunately, importing it as a Flat File did not allow the first row to be treated as column headings (field names) and it assigned Column1, Column2 etc. I then tried by reading my csv into Excel and saved it as .xlsx then used the Excel driver in the Wizard to import but this failed due to a data integrity problem at about row 10,000 (only about half the total)

This was just an exploratory test because although I had read it was possible I have never tried.

S7

[Mihail]

As long as you don't send at least the information I ask I can't help you.
Read again my previous post.

[Amr Ali]

Mihail,

i read your post again and i attached the csv file columns on my reply and regarding your questions:

- How you want to be divided ?
i need it to be separated after every 105 columns and 250,000 row.

- What names you wish for each column ?
as attached in my file.

- What is the max size you need ?
max size that it will be light when making a search query.

[NeoPa]

I have to agree with Rabbit in post #2. This would be a job for a larger capacity system. 800MB is only 40% of capacity for an Access database, but the data has to be imported first. Still, I'm surprised it expands it by as much as 250%.

PS. WordPad is not a text editor. It should never be used for data manipulation in this way. There are various text editors around (My own favourite is TextPad) of which NotePad comes with Windows and can now handle larger files better than the earlier versions.

[Mihail]

@Amr Ali,
I am sure that ADezii's (and NeoPa's and S7's) advices are the best solution for you.
Also I am sure that they will help you to accomplish the task if you decide to do that.

About my request you have omitted to attach a little bit from your .csv file (I need that to play with).