[steve841]

OK .. Im a database noob.

I've had a web site that users search using a .mdb file converted from a .csv into a .xls then imported into a .mdb.

The file has now reached a point where it's too big to open in excel. Needless to say, the searching I have done has not proved successful.

That being said, I need to be able to open this .csv file into excel, save it as a .xls and then import it into my .mdb.

Any suggestions? or is there a better way of doing this?

[savbill]

If you are using Excel 97 - 2003 you can import 65536 rows into a single spreadsheet. If you more reocrds more than this you could save your CSV into 2 files with half the data in each or divide into more files if it is bigger than 131072 records. Excel 2007 can import over 1 million rows.

Your easiest approach to this may be to import the CSV using MS Access and save your MDB with Access. Where you don't have the rows limit.

If you don't have MS Access, that may be why you want to use Excel to convert the data and save as an MDB, it may be worth the expense to abtain a copy of MS Access if your doing this frequently.

An even better option may be to use MySQL or SQL Light as the Back End database for your web site. This will require some programming changes in your site design but I think it would improve your site performance and eliminate the need to convert the data from CSV. Most hosting services offer MySQL database access with thier accounts. There are many scripts configured to read and write to CSV Files which can be access and parsed quickly using a PHP or Pearl script.

[steve841]

Thanks ...

I have both excel and access 2002/2007. Access wont let me import the csv file.. gives me some error about "{filename} does not contain data"

This is corrected (previously) when I opened the .csv in excel and saved as .xls (dont ask me why, but it worked).

Anyhow .. I've seen comments about opening the .csv into 2 files.. Can you explain how to accomplish this? What I read involved a vb code ... and unless its spelled out for me ... it's outta my league.

PS .. its my web server and I'd love to explore MySQL ..but just havent been able to find real guidance. Have a look .. www.npi-search.com

[chergh]

This can be done with VBA but I don't have time to explain it to someone who needs VBA spelled out as you put it. My best advice is to go and buy a book called "VBA and Macros for Microsoft Excel" by Bill Jelen and Tracy Syrstad chapter 18 covers exactly what you want to do and if you ever do pivottables then this book has some great stuff on it.

[savbill]

If you search on your error message "Access file does not contain data" you'll find some posts with people having problems with large files. 2 GB file size seems to cause problems with importing or linking CSVs in Access. You may experiment by cutting the file in 2 parts or smaller files to test if your near 2 GB.

I'm not sure why your needing to take the MDB file converted to a CSV then open it with Excel and finally import to Access? Have you tried using FTP to download your MDB file from the Server? Then you could open that with Access directly and skip the round trip.

here's a post about the Large file size problem
http://www.accessmonster.com/Uwe/For...le-into-Access

I would try to do this without code unless you want to automate it or are doing this very often.

Here's a basic example of using code to import a text file to give you an idea how to put the text into multiple sheets. You'd have to do additional processing to to parse out the CSV values to columns. Change the number after ".CopyFromRecordset" to a smaller number to see how this works, if you have fewer than 65536 line text file when you test it.

Sub ImportLargeFile()
'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.

Dim strFilePath As String, strFilename As String, vFullPath As Variant
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object


'Get a text file name
vFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")

If vFullPath = False Then Exit Sub 'User pressed Cancel on the open file dialog
'Application.ScreenUpdating = False


'This gives us a full path name e.g. C:\folder\file.txt
'We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")

strFilePath = oFSObj.GetFile(vFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(vFullPath).Name


'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Set oRS = CreateObject("ADODB.RECORDSET")

'Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM [" & strFilename & "]", oConn, 3, 1, 1

While Not oRS.EOF
Sheets.Add
ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
Wend

oRS.Close
oConn.Close


End Sub