Importing Data from Access (or text) to Excel 1

[RamziSaab]

I have the following:

- text file (tab delimited) which has data (generated separately) with around 55 million rows and 5 columns (results from another program)

- i need to be able to query the first column (has identifier) and retrieve the data to do calculations in excel (around 55 at a time)...i.e. approx one million sims

what is the quickest method to access and query this data and spit it out in excel

Thanks

 

[SkipVought]

Hi,

Your TITLE implies that the text file is from MS Access. Is this the case?

Skip,

_[red][/red]

 

[RamziSaab]

Yes the data is original generated as a text file, i would like to have all the data in excel but unfortunately due to the limitation on the number or rows i need to just take the data i need and plug it into excel.

Ramzi

 

[SkipVought]

"Yes the data is original generated as a text file, "...

extracted from WHAT system?

Skip,

_[red][/red]

 

[RamziSaab]

an excel program which generates the data (third party)

 

[SkipVought]

An Excel program generated a file with "55 million rows " ??? How did that happen?

Where does "Importing Data from Access (or text) to Excel" come into play?

Skip,

_[red][/red]

 

[firefytr]

Check out these links ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;120596

http://www.danielklann.com/excel/importing_large_text_files_into.htm

http://www.mrexcel.com/archive2/65200/75541.htm

http://www.mrexcel.com/archive/Lotus/4229.html

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[RamziSaab]

the data is generated by an excel c++ directly into a text file

 

[SkipVought]

Where does "Importing Data from Access (or text) to Excel" come into play?

Skip,

_[red][/red]

 

[RamziSaab]

what part is not clear? ineed to get the text file data (or i can link into access) to query it, i want to know what the fastest way to do it is>

 

[RamziSaab]

Firefytr: is there a way to modify the code to query the text file to pull out only ceratin data...(simple query on the 'first column only'

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

Application.ScreenUpdating = True


End Sub

 

[SkipVought]

I wanted to know if the data is ONLY in a text file or it it is ALSO in MS Access?

Since it is in Access, simple use MS Query via Data/Get External Data/New Database Query -- MS Access Databases - -YOUR MS ACCESS DATABASE -- YOUR MS ACCESS TABLE(s) or QUERY(ies)

Pretty simple. Qualify your query using criteria and only import the portion of the 50 mil rows that you need.

Skip,

_[red][/red]

 

[RamziSaab]

is there a way to dynamically change this query, i.e. i want to loop through the field?

 

[SkipVought]

Yes. I do it all the time.

It can be accomplished WITHOUT code or WITH code.

WITHOUT:
In the Criteria that you want to change, instead of a VALUE, enter
[tt]
[?]
[/tt]
When you File/Return data to Excel... you will see a Parameter Button. In Parameters, choose the option to get the parameter from a CELL on your sheet AND to refresh the query when the data in the cell changes.


Skip,

_[red][/red]

 

[RamziSaab]

sorry i can seem to do it i have excel xp,

i go to data --> import external data ---> new database query ---> MS Access ---> choose file --> pick the fields ---> then i get the filter data but it does not let me choose a cell?

thanks

 

[SkipVought]

You don't get to choos a cell until you complete the query and return data to Excel.

Skip,

_[red][/red]

 

[RamziSaab]

i cant seem to do this even when i go to the end

 

[RamziSaab]

ok i think i got it but i had to do it through Microsoft Query (i.e. not in return data to Excel)

 

[SkipVought]

Return Data to Excel is IN Ms Query.

You ALSO need to do the [?] thing in the criteria you want to vary, BEFORE your return data to Excel.

Skip,

_[red][/red]

 

[RamziSaab]

got it to work but it seems just reading the text file is much faster...i guess i will sitck with that thanks for your help