I import a csv file of 2.1 million trucking co’s. Gov’t has moved distribution to a different agency and changed the file layouts/columns. So now have to rewrite the table for Clarion 9.1.
Any suggestions on how to proceed appreciated. Haven’t had to face this in over 10 years.
I don’t know if my example app would barf (run out of memory) on that many records, but it can generate the beginnings of a Clarion file structure for you. If it crashes, try loading fewer records. The structure generator expects labels as the first row of the file.
I’m not sure I get your question … but maybe these thoughts… I would want to preserve the existing Import, so copy the CSV file and Procedures to create an all new Import. If the CSV file is way different then create a new one using the below link.
Search here for CSV and you will find several topics. Below is one I recall.
Do you know how the original import worked?
Is the change significant to the extent that the original structure and technique can’t be updated to match the new structure?
You haven’t provided many details?
The major change is that the fields have been moved around. 4 have been deleted and replaced with other data. There are a total of 144 fields. So it rather a matching process. Thankfully I have the field definitions from before this change. Trying to think how to approach. Interesting that they haven’t changed this since 2006. Looks like the data is unchanged. Every month this data is combined with 3 other data sources with a key.
Maybe have to manually create a new dictionary file. Hope not!!!
“Easiest” way might be to just import the CSV into the dct as a new file and see if you can make use of any existing code that assigns values - or whatever you do with the file?
That way you will at least have the new file structure created correctly and not have to worry about rearranging the old fields?
Don how many MB is the csv file?
I was thinking to use StringTheory as it is just a few lines of code but it would have the same memory limitation if the file is x GB in size as the file is first read into memory then split into lines. Then each line is processed and split into fields.
Hi,
A stripped down piece of code of an import function that I use for CSV file to show how to dynamically change the fields positions for every import.
str StringTheory
lne StringTheory
CODE
! these are columns positions for the fields
! either load them from a ini file, configuration file,
! or display a screen with the first few records of the CSV file and the user can specify or select which field maps to which position
loc:buyer_nr_col = 1
loc:wine_desc_col = 2
loc:qty_col = 3
loc:btls_col = 4
str.LoadFile(loc:input_file)
str.Split('<13,10>','"')
loop x = 1 to str.Records()
Lne.SetValue(Str.GetLine(x))
Lne.Split(',','"','"',true)
if clip(Lne.GetLine(1)) = '' ! or what ever condition to ig nore this specific csv record
cycle
end
loc:buyer_nr = Lne.GetLine(loc:buyer_nr_col)
loc:wine_desc = left(clip(Lne.GetLine(loc:wine_desc_col)))
loc:qty = Lne.GetLine(loc:qty_col)
loc:btls = Lne.GetLine(loc:btls_col)
process here however you need to
End
I’ll plug my Big Bang Theory class on GutHub that will let you view any ST Split in a LIST box using Bang.LinesViewInList(). This is allows you to confirm you got what you thought. You can also view that <13,10> split into columns using Bang.LinesViewSplit(Str,',','"') with any delimiter or for CSV call Bang.LinesViewSplitCSV(Str).
Another ST tool is Split Theory that will let you try out the Load, Split and View of any file using StringTheory without writing any code. It shows you the code you need.
In C9 the ASCII and BASIC drivers were changed to support PREVOUS(). That means if you define your CSV file in the DCT you can wizard a Browse / Form to view it. Only call the Form in View mode, it will need the View extension template. You cannot Change or Delete a CSV file and rewrite the record. Add may be possible.
IIRC the BASIC driver has a bug with PREVIOUS() if you use the First Row Header option when you Page Up it keeps repeating the first row. So you have to turn that off using SEND(File,'FirstRowHeader=OFF') during the Browse. I reported it so maybe @RZaunere fixed it in 12.
4 Likes
Thanks everyone. Looked at this for the last couple of days and thinking it may be simpler to just spend the time and make a new file. I pulled out about 1000 records of the 2 million for easier testing. Sometimes there is a downside to things working so well forgot how I did some routines.