Slow performance Importing small text files

[Barboza Babcock]

We have been importing text using the style below for over 10 years. Frequently a loop will import 80 or more text files for processing one right after another. Earlier versions of excel would tear through the importing and processing of reports. With Excel 365 or Excel 2019 performance is at least 5 times slower. Are we just seeing the effect of software bloat, or do newer versions of Excel just run our legacy style of imports much more slowly?

We tried to design an import using the current text/CSV import interface. Our text files have several rows of text at the top of the page followed by data arranged in columns. If we use the "Legacy" wizard to import data, we can adjust the columns exactly the way we want. The new data import wizard does not appear to offer this flexibility.

So I guess this is a 2 part question.

What gives with the slow performance of newer versions of excel at least as far as data import goes. Frankly, it feels that all the data manipulation we do within excel are sluggish.

Secondly, is the new data/csv import interface only good for files that are neatly arranged in columns throughout the entire page? The user interface is very unfriendly compared to the legacy import interface.

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;F:\" + strLog + "\" + strLabid + ".D\epatemp.txt", Destination:=Range("$A$1"))
        .Name = "epatemp_79"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 9, 1, 1, 1, 9)
        .TextFileFixedColumnWidths = Array(14, 12, 20, 10, 8, 7)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

This is the contents of the text file. Sorry it doesn't line up when copy pasted.

Quantitation Report (Not Reviewed)

Data Path : F:\VOCA\
Data File : S072920.D
Acq On : 29 Jul 2020 2:57 pm
Operator : BP
Sample :
Misc :
ALS Vial : 2 Sample Multiplier: 1

Quant Time: Jul 30 12:00:06 2020
Quant Method : F:\CHEMPC\1\METHODS\VOCA.M
Quant Title : Volatiles
QLast Update : Fri Jul 17 11:42:06 2020
Response via : Initial Calibration

Compound R.T. QIon Response Conc Units Dev(Min)
--------------------------------------------------------------------------
Internal Standards
1) Fluorobenzene 4.695 96 80616 5.00 ug\l -0.01
28) Toluene-D8 6.020 98 81565 5.00 ug\l 0.00
62) 1-bromo-2-chlorobenzene 8.834 192 39466 5.00 ug\l 0.01

System Monitoring Compounds
20) Benzene-D6 4.394 84 75864 5.03 ug\l -0.02
Spiked Amount 5.000 Recovery = 100.60%
44) Bromofluorobenzene 7.562 95 28459 4.89 ug\l 0.00
Spiked Amount 5.000 Recovery = 97.80%
59) 1,2-dichlorobenzene-d4 8.304 152 43110 5.07 ug\l 0.00
Spiked Amount 5.000 Recovery = 101.40%

Target Compounds Qvalue
2) Dichlorodifluoromethane 1.104 85 24690 8.01 ug\l 100
3) Chloromethane 1.229 50 24100 9.69 ug\l 100
4) Vinyl Chloride 1.299 62 28371 10.08 ug\l 100
5) Bromomethane 1.530 94 18360 9.85 ug\l 100
6) Chloroethane 1.606 64 18967 10.30 ug\l 100
7) Trichlorofluoromethane 1.762 101 49899 10.85 ug\l 100
8) 1,1-Dichloroethene 2.200 61 35260 10.02 ug\l 100
9) Methylene Chloride 2.668 84 39944 11.64 ug\l 100
10) MTBE 2.844 73 85988 10.78 ug\l 100
11) trans-1,2-dichloroethene 2.849 61 38684 10.72 ug\l 100
12) 1,1-Dichloroethane 3.242 63 53140 10.46 ug\l 100
13) 2,2-Dichloropropane 3.708 77 47777 10.87 ug\l 100
14) cis-1,2-dichloroethene 3.739 61 42761 10.19 ug\l 100
15) Bromochloromethane 3.945 130 36695 11.14 ug\l 100
16) Chloroform 4.015 83 62840 10.80 ug\l 100
17) 1,1,1-Trichloroethane 4.115 97 59096 10.91 ug\l 100
18) Carbon Tetrachloride 4.218 117 57617 11.53 ug\l 100
19) 1,1-Dichloropropene 4.260 75 43599 10.40 ug\l 100
21) Benzene 4.430 78 157579 11.34 ug\l 100
22) 1,2-Dichloroethane 4.522 62 35093 10.36 ug\l 100
23) Trichloroethene 4.994 95 43771 11.24 ug\l 100
24) 1,2-Dichloropropane 5.236 63 32145 10.66 ug\l 100
25) Dibromomethane 5.340 93 22196 11.30 ug\l 100
26) Bromodichloromethane 5.479 83 49227 10.73 ug\l 100
27) cis-1,3-Dichloropropene 5.867 75 60157 10.67 ug\l 100
29) Toluene 6.070 91 190190 10.97 ug\l 100
30) trans-1,3-Dichloropropene 6.288 75 51044 9.99 ug\l 100
31) Tetrachloroethene 6.410 166 76637 11.81 ug\l 100
32) 1,1,2-Trichloroethane 6.402 97 37675 11.47 ug\l 100
33) 1,3-Dichloropropane 6.508 76 52324 10.73 ug\l 100
34) Dibromochloromethane 6.620 129 51512 11.52 ug\l 100
35) 1,2-Dibromoethane 6.689 107 37502 11.12 ug\l 100
36) Chlorobenzene 6.957 112 148191 11.48 ug\l 100
37) Ethylbenzene 7.002 91 204879 10.76 ug\l 100
38) 1,1,1,2-Tetrachloroethane 7.010 131 56994 11.35 ug\l 100
39) M&P Xylene 7.068 91 328022 21.60 ug\l 100
40) O-Xylene 7.280 91 169932 10.76 ug\l 100
41) Styrene 7.294 104 154523 10.78 ug\l 100
42) Bromoform 7.403 173 42957 11.87 ug\l 100
43) Isopropylbenzene 7.462 105 223262 10.90 ug\l 100
45) Bromobenzene 7.635 156 84049 11.06 ug\l 100
46) N-Propylbenzene 7.668 91 235158 10.79 ug\l 100
47) 1,1,2,2-Tetrachloroethane 7.651 83 32822 11.12 ug\l 100
48) 1,2,3-Trichloropropane 7.679 75 26772 10.89 ug\l 100
49) 2-Chlorotoluene 7.724 126 66283 11.71 ug\l 100
50) 1,3,5-Trimethylbenzene 7.757 105 192269 10.89 ug\l 100
51) 4-Chlorotoluene 7.780 126 69130 11.64 ug\l 100
52) Tert-Butylbenzene 7.908 119 175471 10.93 ug\l 100
53) 1,2,4-Trimethylbenzene 7.939 105 189605 10.91 ug\l 100
54) Sec-Butylbenzene 8.014 105 196432 10.55 ug\l 100
55) P-Isopropyltoluene 8.081 119 212006 10.96 ug\l 100
56) 1,3-Dichlorobenzene 8.086 146 151088 11.92 ug\l 100
57) 1,4-Dichlorobenzene 8.134 146 150507 11.80 ug\l 100
58) N-Butylbenzene 8.273 91 140632 10.54 ug\l 100
60) 1,2-Dichlorobenzene 8.312 146 139457 11.78 ug\l 100
61) 1,2-Dibromo-3-Chloropr... 8.691 157 10166 11.53 ug\l 100
63) 1,2,4-Trichlorobenzene 9.065 180 90671 10.11 ug\l 100
64) Hexachlorobutadiene 9.113 225 52536 10.85 ug\l 100
65) Naphthalene 9.191 128 111046 9.07 ug\l 100
66) 1,2,3-Trichlorobenzene 9.313 180 65885 9.72 ug\l 100
--------------------------------------------------------------------------

(#) = qualifier out of range (m) = manual integration (+) = signals summed

VOCA.M Thu Jul 30 12:00:07 2020

 

[mole999]

Your post has been reported as being cross posted with another site.

Whilst we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: > (Message Board Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s) ASAP.

 

[Barboza Babcock]

This post is also located at: Slow performance Importing small text files
Sorry about not being aware of this issue. Won't happen again. No harm intended. Mea Culpa.