Joined

·
7 Posts

Any help would be greatly appreciated.

Questions or concerns, let me know.

- Status
- Not open for further replies.

1 - 16 of 16 Posts

Joined

·
7 Posts

Any help would be greatly appreciated.

Questions or concerns, let me know.

Joined

·
7,837 Posts

Check out this link:

http://support.microsoft.com/kb/120596

http://support.microsoft.com/kb/120596

Joined

·
5,458 Posts

Code:

```
Sub LargeFileImport()
'Dimension Variables
Dim wb As Workbook, ws As Worksheet
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Long, iRow As Long
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then Exit Sub
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Set wb = Workbooks.Add(template:=xlWorksheet)
Set ws = wb.Sheets(1)
'Set The Counter to 1
Counter = 1: iRow = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ws.Cells(iRow, 1).Value = "'" & ResultStr
Else
ws.Cells(iRow, 1).Value = ResultStr
End If
'For Excel versions before Excel 97, change 65536 to 16384
If iRow = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
iRow = 0
End If
'Increment the Counter By 1
Counter = Counter + 1
iRow = iRow + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub
```

HTH

Joined

·
7 Posts

Now just one more question for you. Once I import the data, I still have to seperate it into columns. I do this by using the code below (just basic text to columns). However, it takes FOREVER (even when I dedicate an entire server to it). Is there a better (or faster) way to perform this task?

Code:

```
Sub delimitedseperation()
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), _
Array(79, 1), Array(80, 1), Array(81, 1)), TrailingMinusNumbers:=True
End Sub
```

ywbd

Joined

·
7,837 Posts

What you decide to do with the data after getting it into Access is up to you - you could filter for certain data, or have an id for each row of data and just show the first 60000 lines in one query, the next 60000 in another and so on (I use 60000 from your first post) and you could copy this data from Access and put it in Excel.

Joined

·
7 Posts

Joined

·
7,837 Posts

Joined

·
5,458 Posts

Can you post a few row examples of your data?

Joined

·
7 Posts

@slurpee55 - Honestly no. I don't really want to get into the reasons, but it would be too hard to use Access.

After playing around, I found that if I have it perform the column separation after each line that it imports (using the aforementioned Microsoft code) then it performs the computations fairly quickly. I can live with it taking ~5 minutes.

Thanks for the help guys. My problem is solved, how do we close this topic?

ywbd

Joined

·
7,837 Posts

Joined

·
5,458 Posts

Joined

·
7,837 Posts

Now, now...shall we post bank statements online? You first.

Joined

·
5,458 Posts

Joined

·
7,837 Posts

Rats!

Joined

·
2,255 Posts

1,111,111,111,111,111.00 overdrawn

Joined

·
7,837 Posts

Thank you!!!!Now, how to spend it.... hee, hee: cool:

1 - 16 of 16 Posts

- Status
- Not open for further replies.

Join the discussion

Tech Support Guy

A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!

Full Forum Listing
Explore Our Forums

Recommended Communities

Join now to ask and comment!