Separate data at import?

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
I am about to import a large text file (9MB) into excel and have encountered a problem. The file is too big to load in, > 65000 lines available. As I would be wanting to separate the data across a number of sheets, would there be a way to "post" each line to a specific sheet based on the first few characters of each line of data. And thus allow the whole sheet to load?
Or is there something else that could assist me with this challenge?

As always I'm ever grateful for any suggestions.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
NCay,

Here´s a procedure that will solve it in a smooth way:

<PRE>
<FONT color=blue>Sub </FONT>LäsaInStoraTextFiler()

<FONT color=blue>Dim </FONT>stResultat<FONT color=blue> As</FONT><FONT color=blue> String</FONT>, stFilNamn<FONT color=blue> As</FONT><FONT color=blue> String</FONT>

<FONT color=blue>Dim </FONT>lnFilNamn<FONT color=blue> As</FONT><FONT color=blue> Long</FONT>

<FONT color=blue>Dim </FONT>i<FONT color=blue> As</FONT><FONT color=blue> Double</FONT>



stFilNamn = Application.GetOpenFilename



<FONT color=blue>If </FONT>stFilNamn = ""<FONT color=blue> Then </FONT>End



lnFilNamn = FreeFile()



<FONT color=blue>Open </FONT>stFilNamn <FONT color=blue>For </FONT>Input<FONT color=blue> As</FONT> #lnFilNamn

Application.ScreenUpdating =<FONT color=blue> False</FONT>



<FONT color=blue> Workbook</FONT>s.Add Template:=xlWorksheet



i = 1



<FONT color=blue>Do </FONT>While Seek(lnFilNamn) <= LOF(lnFilNamn)

Application.StatusBar = "Import row " & _

i & " from file " & stFilNamn



Line Input #lnFilNamn, stResultat



<FONT color=blue>If </FONT>Left(stResultat, 1) = "=" Then

ActiveCell.Value = "'" & stResultat

<FONT color=blue>Else</FONT>

ActiveCell.Value = stResultat

<FONT color=blue>End If</FONT>



<FONT color=blue>If </FONT>ActiveCell.Row = 65536 Then

ActiveWorkbook.Sheets.Add

<FONT color=blue>Else</FONT>

ActiveCell.Offset(1, 0).Select

<FONT color=blue>End If</FONT>



i = i + 1

<FONT color=blue>Loop</FONT>



Close



Application.StatusBar =<FONT color=blue> False</FONT>



<FONT color=blue>End Sub</FONT>
</PRE>

Kind regards,
Dennis
 

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
A thousand thankyous, I'll go and experiment with it just now.
Can I come back to you if I have any queries/ problems though? Expect a few silly questions from me later... :biggrin:
 

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
On 2002-09-05 17:56, NCay wrote:
A thousand thankyous, I'll go and experiment with it just now.
Can I come back to you if I have any queries/ problems though? Expect a few silly questions from me later... :biggrin:

You welcome and if I´m not around there are excellent regular contributors that also can help You :)

Kind regards,
Dennis
 

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
Ok #1 question, I've just run the sub and it crashed with "run time error 62, input past end of file". The data it has "worked through" is all on one sheet. Is the purpose of this to separate different lines to different sheets, and if so do I have to specify what and where?
Maybe further explanation is required for me.
Any advice?

PS it stopped about half way through
This message was edited by NCay on 2002-09-05 18:16
 

Forum statistics

Threads
1,144,059
Messages
5,722,272
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top