PDA

View Full Version : VBA Import Text Line by Line to formatted Cells



Kalain
03-17-2017, 07:03 AM
Hi Guys,
I have the following problem:


I will import some very large Logs (*.txt files)
Source looks like this:



id:name:IO_group_id:IO_group_name:status:mdisk_grp_id:mdisk_grp_name:capaci ty:type:FC_id:FC_name:RC_id:RC_name:vdisk_UID:fc_map_count:copy_count:fast_ write_state:se_copy_count:RC_change:compressed_copy_count:parent_mdisk_grp_ id:parent_mdisk_grp_name:formatting:encrypt:volume_id:volume_name:function
0:BCAS_IBMi_Template_V7R2:1:io_grp1:online:0:BCSTG01-DEMO-Power:250.00GB:striped:::::6005076801818772D000000000000000:0:1:empty:0:no: 0:0:BCSTG01-DEMO-Power:no:no:0:BCAS_IBMi_Template_V7R2:
1:BCAS_BCSTG01_Live_Source_Lun001:1:io_grp1:online:0:BCSTG01-DEMO-Power:250.00GB:striped:many:many:::6005076801818772D000000000000003:3:1:not _empty:0:no:1:0:BCSTG01-DEMO-Power:no:no:1:BCAS_BCSTG01_Live_Source_Lun001:
2:BCAS_Mimix01_Target_Lun001:1:io_grp1:online:0:BCSTG01-DEMO-Power:250.00GB:striped:::::6005076801818772D000000000000004:0:1:not_empty:0 :no:1:0:BCSTG01-DEMO-Power:no:no:2:BCAS_Mimix01_Target_Lun001:
3:BCAS_IBMi_Template_V7R1:1:io_grp1:online:0:BCSTG01-DEMO-Power:250.00GB:striped:::::6005076801818772D000000000000005:0:1:empty:0:no: 0:0:BCSTG01-DEMO-Power:no:no:3:BCAS_IBMi_Template_V7R1:
4:BCAS_BCAS001_Lun001:0:io_grp0:online:0:BCSTG01-DEMO-Power:150.00GB:striped:::::6005076801818772D000000000000006:0:1:empty:0:no: 0:0:BCSTG01-DEMO-Power:no:no:4:BCAS_BCAS001_Lun001:
5:BCAS_BCAS001_Lun002:0:io_grp0:online:0:BCSTG01-DEMO-Power:150.00GB:striped:::::6005076801818772D000000000000007:0:1:empty:0:no: 0:0:BCSTG01-DEMO-Power:no:no:5:BCAS_BCAS001_Lun002:
6:BCAS_BCAS002_Lun001:1:io_grp1:online:0:BCSTG01-DEMO-Power:240.00GB:striped:::::6005076801818772D000000000000008:0:1:empty:0:no: 0:0:BCSTG01-DEMO-Power:no:no:6:BCAS_BCAS002_Lun001:


I've imported them yet with this code:



With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & lsvdiskf, Destination _
:=Range("$A$1"))
.Name = "lsvdisk_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ":"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh


Now I have the Problem, that I run into a Runtime Error (Not enough ressources).


I think the module is going too big by the import task.


So I will import 10 lines from the file, and paste them (splitted into cells) - and again - and again till the end of file is reached.


I've looked in the WWW - found some ideas - but nothing that fits to me.


Do anyone have an idea? - Or: Is it possible?


Thank you very much!


BR,
Kalain

mdmackillop
03-17-2017, 02:11 PM
Can you post a sample log file of approx. 100 rows?

p45cal
03-17-2017, 02:13 PM
You could try something like (stolen from http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba ):
Sub blah()
Dim FileNum As Integer
Dim DataLine As String

FileNum = FreeFile()
Open lsvdiskf For Input As #FileNum
Set Destn = Range("A1")
While Not EOF(FileNum)
Line Input #FileNum, DataLine ' read in data 1 line at a time
' decide what to do with dataline,
' depending on what processing you need to do for each case
Z = Split(DataLine, ":")
Destn.Resize(, UBound(Z) + 1).Value = Z
Set Destn = Destn.Offset(1)
Wend
End Subbut I have no idea whether it will use fewer resources!


Are you going to filter/remove rows after import? If so you could avoid importing these rows at all by using an sql query with ADO or similar.


Ahh, I see you've cross posted to that site anyway (http://stackoverflow.com/questions/42841399/vba-import-text-line-by-line-to-formatted-cells)

Kalain
03-20-2017, 01:05 AM
Sorry for this late answer..

Well, I won't filter or remove lines after import. My idea is:
When I copy and paste maybe 10 lines at ones, the module cache wont get too big and I dont run into the resources problem.

BR,
Flo