Reading a text file

[GaryVass]

First, does the new version (out soon???) have a working version of Open and Line Input?

I have read the boards that recommend using ReadList as a substitution, but have not seen a working example, and have been unable to get it to work. I was gong to use tnum as a counter to read each ticker separately to use for writing to the mysql database (have not finished coding that part).

I need to read a list of tickers from a file and write it to a mysql database. I have the following code, so far (note: some lines are commented out, as I changed code to temporarily use ReadList) :

Dim scrTic
Dim ticList As List
Dim tnum As Integer
tnum=0
fp1 = FreeFile()

If gsDatabase Then
strSQL = "DELETE FROM LowPEscreen;"
rslt = Query.Run(strSQL)

MsgBox("Entered Screen Import Module", rslt) '
'Open "c:\naic\screen.csv" For Input Access Read As fp1 ' Open file for input.
ReadList("c:\naic\screen.csv") As ticList
Do While tnum > 0
'Do While EOF(fp1)= False
' Line Input #fp1, scrTic
scrTic = ticList[tnum]
MsgBox("Ticker being processed is: ", scrTic)

strSQL = "INSERT INTO LowPEscreen (TIC, buylst) VALUES ('" & scrTic & "', 'Y');"
rslt = Query.Run(strSQL)
tnum = tnum-1
'
Loop
End If

End Sub

[berndnoetscher]

The new version will come soon. Please post some of the contents of your data file you are talking about, and I will create you a working snippset.

First, does the new version (out soon???) have a working version of Open and Line Input?

I have read the boards that recommend using ReadList as a substitution, but have not seen a working example, and have been unable to get it to work. I was gong to use tnum as a counter to read each ticker separately to use for writing to the mysql database (have not finished coding that part).

I need to read a list of tickers from a file and write it to a mysql database. I have the following code, so far (note: some lines are commented out, as I changed code to temporarily use ReadList) :

Dim scrTic
Dim ticList As List
Dim tnum As Integer
tnum=0
fp1 = FreeFile()

If gsDatabase Then
strSQL = "DELETE FROM LowPEscreen;"
rslt = Query.Run(strSQL)

MsgBox("Entered Screen Import Module", rslt) '
'Open "c:\naic\screen.csv" For Input Access Read As fp1 ' Open file for input.
ReadList("c:\naic\screen.csv") As ticList
Do While tnum > 0
'Do While EOF(fp1)= False
' Line Input #fp1, scrTic
scrTic = ticList[tnum]
MsgBox("Ticker being processed is: ", scrTic)

strSQL = "INSERT INTO LowPEscreen (TIC, buylst) VALUES ('" & scrTic & "', 'Y');"
rslt = Query.Run(strSQL)
tnum = tnum-1
'
Loop
End If

End Sub

[Slowdown]

Hi GaryVass,

"c:\naic\screen.csv"

Probably the data in your file is separated by ';' or some other character in that case you can use,
(did not tested this code but have created something similar, this is from my head)

Code: Select all

Dim ReadList As List
ReadLst = Split(ReadCString("C:/naic/screen.csv"), ";", False)

Yes / instead of \
Readlist can be readout by,

Code: Select all

Dim Lus As Int64
Dim MyString As String
For Lus = 1 To ReadList.Length() - 1
  MyString = ReadList.Object(Lus)
Next


Hope that this helped you somehow.

[GaryVass]

Ok,

I have worked through the examples, and now have data being imported.

I do have some quirks... the length of the file, as reported in the line :
tnum = Len("c:/naic/screen.csv")

is off, by half. I have mulitiplied by two in the code below to get around this, but I don't see a reason as to why it is happening.

Second, my data is several fields enclosed in quotes and separated by commas. How do I get the code to ignore the commas that are enclosed in quotes? I have copied the one field I need into a separate file, and then added commas in notepad (strangely, when excel saves the file as a csv text, the code below can't seem to find any commas??? I assume excel is just not saving the commas in the file).

Sugestions? (and thank you for the help you have provided to this point.... it is appreciated).

My code, as being used now, is copied below:

ticList = Split(ReadCString("c:/naic/screen.csv"), ",", False )
tnum = Len("c:/naic/screen.csv")*2
tnum = tnum

For Lus = 1 To tnum
scrTic = ticList.Object(Lus)
strSQL = "INSERT INTO LowPEscreen (TIC, buylst) VALUES ( '" & scrTic & "', 'Y');"
rslt = Query.Run(strSQL)
Next

[Slowdown]

Hi GaryVass,

I do have some quirks... the length of the file, as reported in the line :
tnum = Len("c:/naic/screen.csv")

tnum is 18 i suppose
tnum returns the length of the string "c:/naic/screen.csv"
That is not the way to get the file size use this,

Code: Select all

Dim tnum As Integer
tnum = File.Size("c:/naic/screen.csv")

Second, my data is several fields enclosed in quotes and separated by commas. How do I get the code to ignore the commas that are enclosed in quotes?

Code: Select all

Dim ReadList As List
ReadLst = Split(ReadCString("C:/naic/screen.csv"), ",", False)

Now your data is placed into the array ReadList
To check if the array is filled do,

Code: Select all

stdout (ReadList.Length())
or
MsgBox(ReadList)

the output with stdout must be > 0
And depending the size of the array the MsgBox will fill your screen
If it is not top secret and not to big zip your data file and upload it to the forum i will
make the part to load the csv file into an array.

[GaryVass]

Slowdown,

I can upload the file.... it is simply a screen result from an investment screen. The file I used orginally (that I will upload) has more data that I need to import. I had written code to ignore the extra data, but it was picking up the commas that were part of the company name even though my line to read the file into a list was the same as the one you posted in the prior message.

I changed the line to determine the file size... it now reports 239, but it should only be about 37.... as there are 37 entries to be imported??

[GaryVass]

Ok, I have tried to upload as a file attachment but the system would not let me... I have copied it below... it should be saved as a .csv file.

Thanks.

"No.","Ticker","Company","Sector","Industry","Country","Market Cap","P/E","Price","Change","Volume"
1,"AEM","Agnico-Eagle Mines Ltd.","Basic Materials","Gold","Canada",7514.22,66.19,44.35,-1.18%,1355146
2,"AMRS","Amyris, Inc.","Healthcare","Biotechnology","USA",498.77,,10.95,-2.84%,373364
3,"ANF","Abercrombie & Fitch Co.","Services","Apparel Stores","USA",4162.69,21.57,47.88,-0.06%,1460317
4,"APKT","Acme Packet, Inc.","Technology","Communication Equipment","USA",2267.98,47.59,33.79,1.08%,2435985
5,"BRLI","Bio-Reference Laboratories Inc.","Healthcare","Medical Laboratories & Research","USA",338.75,9.85,12.12,-2.26%,233697
6,"CECO","Career Education Corp.","Services","Education & Training Services","USA",549.15,3.55,7.24,2.55%,1684482
7,"CODE","Spansion Inc.","Technology","Semiconductor- Memory Chips","USA",521.64,2.00,8.46,-3.20%,232614
8,"CPSI","Computer Programs & Systems Inc.","Technology","Healthcare Information Services","USA",513.29,19.67,46.41,2.22%,127613
9,"CROX","Crocs, Inc.","Consumer Goods","Textile - Apparel Footwear & Accessories","USA",1393.61,12.45,15.44,-0.45%,1924165
10,"DXCM","DexCom, Inc.","Healthcare","Medical Instruments & Supplies","USA",540.08,,8.00,-0.25%,572326
11,"FN","Fabrinet","Consumer Goods","Electronic Equipment","Cayman Islands",486.96,7.53,14.16,-0.21%,181959
12,"FRAN","Francesca's Collections, Inc.","Services","Specialty Retail, Other","USA",668.77,35.72,15.36,-6.91%,521085
13,"GIL","Gildan Activewear Inc.","Consumer Goods","Textile - Apparel Clothing","Canada",1986.27,8.06,16.36,-31.49%,5770520
14,"GPL","Great Panther Silver Ltd","Basic Materials","Silver","Canada",305.24,20.55,2.26,-0.88%,1091077
15,"HFC","HollyFrontier Corporation","Basic Materials","Oil & Gas Refining & Marketing","USA",4902.26,4.51,23.43,0.77%,2444310
16,"ILMN","Illumina Inc.","Healthcare","Biotechnology","USA",3393.03,35.82,27.94,0.43%,1489329
17,"IPGP","IPG Photonics Corporation","Technology","Semiconductor - Integrated Circuits","USA",1928.70,17.34,40.57,5.84%,703538
18,"ITMN","InterMune Inc.","Healthcare","Biotechnology","USA",1205.38,11.72,18.40,1.32%,700569
19,"KH","China Kanghui Holdings","Healthcare","Medical Appliances & Equipment","China",350.25,23.08,15.00,-1.64%,99879
20,"MERC","Mercer International Inc.","Consumer Goods","Paper & Paper Products","Canada",320.18,2.79,5.74,-3.20%,308148
21,"MFB","Maidenform Brands, Inc.","Consumer Goods","Textile - Apparel Clothing","USA",428.17,9.90,18.22,-1.19%,172492
22,"MFN","Minefinders Corp. Ltd.","Basic Materials","Industrial Metals & Minerals","Canada",979.36,38.81,12.03,0.33%,508185
23,"MMYT","MakeMyTrip Limited","Services","General Entertainment","India",874.08,124.84,23.72,-6.39%,206427
24,"MTZ","MasTec, Inc.","Industrial Goods","Heavy Construction","USA",1342.02,10.29,15.85,-1.00%,930120
25,"MWA","Mueller Water Products, Inc.","Industrial Goods","Industrial Equipment & Components","USA",326.74,,2.10,-6.25%,1481276
26,"NUVA","NuVasive, Inc.","Healthcare","Medical Appliances & Equipment","USA",580.80,,13.75,-0.36%,421294
27,"OVTI","OmniVision Technologies Inc.","Technology","Semiconductor - Integrated Circuits","USA",683.13,4.60,11.46,6.21%,4280392
28,"PWER","Power-One Inc.","Technology","Diversified Electronics","USA",438.81,4.05,4.21,-1.17%,1658783
29,"SFLY","Shutterfly, Inc.","Services","Consumer Services","USA",955.51,65.62,27.56,1.77%,2053581
30,"SIMG","Silicon Image, Inc.","Technology","Semiconductor - Broad Line","USA",397.75,161.67,4.85,-1.22%,612824
31,"SSRI","Silver Standard Resources Inc.","Basic Materials","Silver","Canada",1176.25,2.32,14.59,-0.82%,1052761
32,"SWKS","Skyworks Solutions Inc.","Technology","Semiconductor - Integrated Circuits","USA",3010.28,13.58,16.16,-0.92%,8159575
33,"TQNT","TriQuint Semiconductor, Inc.","Technology","Semiconductor - Integrated Circuits","USA",761.33,9.16,4.58,4.81%,4971926
34,"TX","Ternium S.A.","Basic Materials","Steel & Iron","Luxembourg",3353.86,6.80,16.73,0.30%,915677
35,"WFR","MEMC Electronic Materials Inc.","Technology","Semiconductor - Integrated Circuits","USA",961.06,,4.17,0.00%,9782124
36,"WNR","Western Refining Inc.","Basic Materials","Oil & Gas Refining & Marketing","USA",1156.01,7.03,12.73,7.06%,4755313

[Slowdown]

Hi GaryVass,

This is a nasty one
If the cell's are delimited with a ',' the ',' may not be used anywhere else.
I will look into it and let you know.

[Henning]

And I really miss the "Standard" file I/O functions, Input#, Line Input#, Read#, Write#, Print#.

In Excel you can set the delimiter to ; when saving as .skv.

/Henning

[Henning]

This is after load .csv into Excel and save as .skv.

No.;Ticker;Company;Sector;Industry;Country;Market Cap;P/E;Price;Change;Volume
1;AEM;Agnico-Eagle Mines Ltd.;Basic Materials;Gold;Canada;7514.22;66.19;44.35;-1.18%;1355146
2;AMRS;Amyris, Inc.;Healthcare;Biotechnology;USA;498.77;;10.95;-2.84%;373364
3;ANF;Abercrombie & Fitch Co.;Services;Apparel Stores;USA;4162.69;21.57;47.88;-0.06%;1460317
4;APKT;Acme Packet, Inc.;Technology;Communication Equipment;USA;2267.98;47.59;33.79;1.08%;2435985
5;BRLI;Bio-Reference Laboratories Inc.;Healthcare;Medical Laboratories & Research;USA;338.75;9.85;12.12;-2.26%;233697
6;CECO;Career Education Corp.;Services;Education & Training Services;USA;549.15;3.55;7.24;2.55%;1684482
7;CODE;Spansion Inc.;Technology;Semiconductor- Memory Chips;USA;521.64;2.00;8.46;-3.20%;232614
8;CPSI;Computer Programs & Systems Inc.;Technology;Healthcare Information Services;USA;513.29;19.67;46.41;2.22%;127613
9;CROX;Crocs, Inc.;Consumer Goods;Textile - Apparel Footwear & Accessories;USA;1393.61;12.45;15.44;-0.45%;1924165
10;DXCM;DexCom, Inc.;Healthcare;Medical Instruments & Supplies;USA;540.08;;8.00;-0.25%;572326
11;FN;Fabrinet;Consumer Goods;Electronic Equipment;Cayman Islands;486.96;7.53;14.16;-0.21%;181959
12;FRAN;Francesca's Collections, Inc.;Services;Specialty Retail, Other;USA;668.77;35.72;15.36;-6.91%;521085
13;GIL;Gildan Activewear Inc.;Consumer Goods;Textile - Apparel Clothing;Canada;1986.27;8.06;16.36;-31.49%;5770520
14;GPL;Great Panther Silver Ltd;Basic Materials;Silver;Canada;305.24;20.55;2.26;-0.88%;1091077
15;HFC;HollyFrontier Corporation;Basic Materials;Oil & Gas Refining & Marketing;USA;4902.26;4.51;23.43;0.77%;2444310
16;ILMN;Illumina Inc.;Healthcare;Biotechnology;USA;3393.03;35.82;27.94;0.43%;1489329
17;IPGP;IPG Photonics Corporation;Technology;Semiconductor - Integrated Circuits;USA;1928.70;17.34;40.57;5.84%;703538
18;ITMN;InterMune Inc.;Healthcare;Biotechnology;USA;1205.38;11.72;18.40;1.32%;700569
19;KH;China Kanghui Holdings;Healthcare;Medical Appliances & Equipment;China;350.25;23.08;15.00;-1.64%;99879
20;MERC;Mercer International Inc.;Consumer Goods;Paper & Paper Products;Canada;320.18;2.79;5.74;-3.20%;308148
21;MFB;Maidenform Brands, Inc.;Consumer Goods;Textile - Apparel Clothing;USA;428.17;9.90;18.22;-1.19%;172492
22;MFN;Minefinders Corp. Ltd.;Basic Materials;Industrial Metals & Minerals;Canada;979.36;38.81;12.03;0.33%;508185
23;MMYT;MakeMyTrip Limited;Services;General Entertainment;India;874.08;124.84;23.72;-6.39%;206427
24;MTZ;MasTec, Inc.;Industrial Goods;Heavy Construction;USA;1342.02;10.29;15.85;-1.00%;930120
25;MWA;Mueller Water Products, Inc.;Industrial Goods;Industrial Equipment & Components;USA;326.74;;2.10;-6.25%;1481276
26;NUVA;NuVasive, Inc.;Healthcare;Medical Appliances & Equipment;USA;580.80;;13.75;-0.36%;421294
27;OVTI;OmniVision Technologies Inc.;Technology;Semiconductor - Integrated Circuits;USA;683.13;4.60;11.46;6.21%;4280392
28;PWER;Power-One Inc.;Technology;Diversified Electronics;USA;438.81;4.05;4.21;-1.17%;1658783
29;SFLY;Shutterfly, Inc.;Services;Consumer Services;USA;955.51;65.62;27.56;1.77%;2053581
30;SIMG;Silicon Image, Inc.;Technology;Semiconductor - Broad Line;USA;397.75;161.67;4.85;-1.22%;612824
31;SSRI;Silver Standard Resources Inc.;Basic Materials;Silver;Canada;1176.25;2.32;14.59;-0.82%;1052761
32;SWKS;Skyworks Solutions Inc.;Technology;Semiconductor - Integrated Circuits;USA;3010.28;13.58;16.16;-0.92%;8159575
33;TQNT;TriQuint Semiconductor, Inc.;Technology;Semiconductor - Integrated Circuits;USA;761.33;9.16;4.58;4.81%;4971926
34;TX;Ternium S.A.;Basic Materials;Steel & Iron;Luxembourg;3353.86;6.80;16.73;0.30%;915677
35;WFR;MEMC Electronic Materials Inc.;Technology;Semiconductor - Integrated Circuits;USA;961.06;;4.17;0.00%;9782124
36;WNR;Western Refining Inc.;Basic Materials;Oil & Gas Refining & Marketing;USA;1156.01;7.03;12.73;7.06%;4755313

/Henning