Import text file & format

[Nitin Suntwal]

Hi There,

I have attached a text file & my macro workbook. Let me take through the steps what I am doing on daily basis.

I get a text file size will be around 4 to 5 MB or sometimes less than that. I have around 270 columns & 70K rows.

1. I have to import text file which I have to separate with delimited char is "^" & the whole data should be in text format, which is done by recording macro.

2. Column G is amount ending with + or – I have macro for that.

3. Column H is date I have change it into MM/DD/YYYY format I have the macro for that.

The problem is I 70 columns for which I have to change the date format. Columns are fixed for date H,BA,BG,BH,BI,BJ,HA, & so on. The same goes with amount 30 columns.

I have loop to change the format of date & amount, however what I am doing is copy & paste the loop code & changing the columns from H to BA then BG like this 70 times I have copied to change the date & 30 times to change the amount columns. With that vba is giving error as procedure too large. Then I have broken the macro into 3 steps & call all those three with a button. It’s running but it’s slow.

Can someone help me with this issue?

Thanks in advance.

 

[Hui]

How about this

Sub Macro1()
'
' Macro1 Macro
'

'
  With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Users\Dads\Downloads\Delimited File.txt", _
  Destination:=Range("$A$2"))
  .Name = "Delimited File"
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .TextFilePromptOnRefresh = False
  .TextFilePlatform = 437
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierNone
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = True
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = False
  .TextFileSpaceDelimiter = False
  .TextFileOtherDelimiter = "^"
  .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2)
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
  End With

  For Each c In Array(8, 10) ' Change this to Array(8, 10, x, y , z)
'where x, y & z are the column Numbers for the other date columns
  ConvertDate (c)
  Next c

End Sub

Function ConvertDate(Col As Integer)

  Cells(2, Col).Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.TextToColumns _
  Destination:=Cells(2, Col), _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=False, _
  Tab:=True, _
  Semicolon:=False, _
  Comma:=False, _
  Space:=False, _
  Other:=False, _
  FieldInfo:=Array(1, 5), _
  TrailingMinusNumbers:=True
   
End Function

 

[stefanoste78]

Good morning.

How would the macro become when there are more txt files?

I would like the macro to open so many pages excel (in the same file) how many text files are in the "text" folder on the desktop and paste the contents of each text file on each excel page.

The macro should not only import the data on every excel page, but it must also include the title of the columns, which I will put in the "list" page. If I modify the name of the columns on the page list or insert new columns one day, the table columns of all the pages in the excel file will automatically be updated.

Once done this I would like to have hyperlinks that return to the page with the same name.

I attach an example

 

[Hui]

Good morning.

How would the macro become when there are more txt files?

...

Please start a new post
goto: http://forum.chandoo.org/
select the appropriate Forum
then start a newThread


Maybe also read: New Users Please Read at:
http://forum.chandoo.org/forums/new-users-please-start-here.14/