Recent content by sumdumgai

Thank you. Data comes in as a .DAT file, almost 8 mil. records I found after using a free text editor that can accommodate the file size. I'm reading the data into an array, splitting the file into lines and then lines into fields, adding only those records that match a year_month value to the...

Just to update you, I've managed to load all of the data (24 months worth) in a database. I first had to remove the table indices and then load the data one month at a time. After all months were loaded, I reset the table indices. The database size is 1.9 GB. There is another table that is...

Thank you.

Well, it looks like loading the data into a non-indexed table works, but loading into an indexed table does not. Without indices, loading 24 months of data creates a database that is 1.725 GB. Loading into an indexed table loads about 20 months and then stops. Haven't figured out why it stops...

Thank you for replies. The reason I'm asking is that I'm loading so much data that it's approaching the 2GB limit. I was able to load the data without indexing set in the table design and then add the indices afterwards. Still within 2GB. But, when I loaded the table with indices already...

Hello all, I've got a question regarding indexing and database size. Does anyone know if the database size will differ if the table design includes indexed fields and the table is then loaded with data vs. the table design does not include indexed fields, is loaded and then indices are added...

Thanks CJ. Don't know what you mean by normalizing. The data comes in as records separated by LF and CR's. Can you please explain?

Thank you all for the MS SQL suggestion. What if I stuck with MS Access and loaded the data by year into different databases. Then link the tables in another pseudo database, and run queries there against all three data databases (e.g., 2021, 2022, 2023). Every month, either add a new "month"...

Thanks. Is that free software? Is it similar to Microsoft SQL Server 2022, which is free and can handle 10GB?

Hello all, We're trying to build a new Access database with .dat data that is downloaded from a remote site. The problem is that there is too much data and the 2GB limit size on the database is being exceeded. Can I please get some recommendations on what to do? The data coming in cannot be...

Thanks to all. I think I have it solved by filtering out unwanted lines before adding to array.

I thought of a simple solution and that is to filter the text file to remove lines that are not needed before it is imported. In the interest of saving time, can someone please provide some sample code to read a line of text (tab delimited and ending with LF), check if a particular field...

I read a line to a string and then store it in the array. strTextLine = .ReadLine lineIndex = lineIndex + 1 ReDim Preserve arrLines(1 To lineIndex) arrLines(lineIndex) = strTextLine

Thanks to all for your suggestions. The code to process these text files was developed long ago and I'd rather not reinvent the wheel. Right now, I'm trying to read the text file into the array in chunks, using a max line count safety value (e.g. 100,000) to limit memory use, and starting line...

The data is appended to existing data, and importing same data twice is not allowed. Some records and/or fields are ignored, others are modified, line by line as they are read in from the array using VBA.