I was reading Much ADO about Text Files: http://msdn.microsoft.com/en-us/library/ms974559.aspx
I am trying to make a somewhat universal UI for taking data files (Access, Excel, Fixed Width Text, and Delimited Text) and sending them to MS SQL server in a string. I've already built something but it has file size limitations as it loads the data into a single string. I need to find a way to split the files into chunks so that I can process large files (I was given a 750MB text file as the worst case scenario example). I also think the chunking will add some benefits of being able to report progress to the user, especially with large files. It might even open the door to allowing a pause/resume for really big files!
Here is what I'm doing now:
Text: open the file using 'Open filename for Input' and then using strOutput = Input$(LOF(FileNum), FileNum) to send the entire file into a string - as/is. As I mentioned - this works pretty well, until I get files that exceed the limitations of a string. This process has served me well for the past year, but it won't work for the new process where there can be significantly larger files.
Access/Excel - open an ADO connection and use If Not rs.EOF Then strOutput = rs.GetString(, , strColumnDelimiter, strRowDelimiter)
After reading much ADO about text files, it seems to me that perhaps opening the text files in ADO would be my best method - because then Access, Excel, and Text will use the same source (ADO recordset) and I can build a process that splits the recordset into chunks and sends them up to SQL.
My problem: First of all, if I understood properly, you have to have a registry entry or an INI file to send these text files to a recordset? Secondly, with Excel and Access it's alright to convert the data into a delimited string, but with Text I want to keep each row intact, exactly as it was received on the input file and send it to SQL where we can handle the parsing and centrally store the file layouts. Is there a way I could build some kind of universal INI file that would keep the rows intact? Perhaps a varchar(MAX) column. Maybe treat it as a delimited file and use some kind of random character that would never appear on a file - a foreign sybmbol, null, or something else? This is just a thought? I just need the user to be able to select ANY text file and send it up to SQL. SQL will piece the chunks back together, and let the user decide how to parse it. We want to keep the source files intact on a somewhat universal table we're building.
If anyone thinks there is a better method than ADO, let me know! I had been happy with the basic file i/o stuff since I do want to keep it intact. The benefit of using ADO is that it's the same methodology I use for Access/Excel. However, if it's too hard to keep the rows exactly as they were sent to me, maybe I need to just use the Open file option and use Line Input.