CSV to fixlength data files
CSV to fixlength data files
Hi all,
I would like to run a idea past you all.
I am writing a dos based till app and I need to get the data from a VB.net application that I struggling to output fix length data files. So would this idea work
When the software boots have it copy from a shared folder a CSV file of all the products and then import it into the fix length data file.
That’s bit is not a problem my problem comes how do I do fixes length data files. (If any know of a good example PLEASE post the link and I will have a read and work from there) the main reason what I ask about this is I have a data file that has over 7,000 lines in it and it is extreamly slow at reading data from the product.dat file (this is a csv file) so if I imported it st the start of the application into the fixed length database it should (if I have my understand of this right) run a lot faster and be more accurate with the results)
I would like to run a idea past you all.
I am writing a dos based till app and I need to get the data from a VB.net application that I struggling to output fix length data files. So would this idea work
When the software boots have it copy from a shared folder a CSV file of all the products and then import it into the fix length data file.
That’s bit is not a problem my problem comes how do I do fixes length data files. (If any know of a good example PLEASE post the link and I will have a read and work from there) the main reason what I ask about this is I have a data file that has over 7,000 lines in it and it is extreamly slow at reading data from the product.dat file (this is a csv file) so if I imported it st the start of the application into the fixed length database it should (if I have my understand of this right) run a lot faster and be more accurate with the results)
Re: CSV to fixlength data files
I occasionally use a 19 MB data file taken straight from the United Nations Statistical Office (direct download, takes a while). Loading the 44,500 lines into a string array takes typically 60 milliseconds on my mediocre Core i5 notebook. Sorting that string array by one of the columns costs around 30ms.Gablea wrote:I have a data file that has over 7,000 lines in it and it is extreamly slow at reading data from the product.dat file (this is a csv file)
Maybe you should check your loading routine instead of going for a fixed size solution?
Re: CSV to fixlength data files
This is my functionjj2007 wrote:Maybe you should check your loading routine instead of going for a fixed size solution?
Code: Select all
If RecipitClear = "Yes" Then
CreateRecipitHeadder
CreateRecipitFooter
ResetPoSdataForNewSale
End If
Dim ProductFound As Integer = 0
CloseAllFiles
Dim r1 As Integer = Open(PathToProductDatabase For Input As #ProductFileNumber)
If DebugMode = 1 Then
Open Cons For Output As #DebugConsole
Print #DebugConsole, PathToProductDatabase
Print #DebugConsole, " File hadling Number : " & ProductFileNumber
Print #DebugConsole, " Sub Name : FindProductinDatabase"
Print #DebugConsole, "barcode Number to Find : " & BarcodeNumber
Print #DebugConsole, " file Open result : " & r1
Close #DebugConsole
End if
If r1 <> 0 Then
If DebugMode = 1 Then
Open Cons For Output As #DebugConsole
Print #DebugConsole, "Error opening the file - error "; r1
Print #DebugConsole, PathToProductDatabase
Print #DebugConsole, "Sub Name : FindProductinDatabase"
Close #DebugConsole
End If
CloseAllFiles
Exit Sub
Else
Do until EOF(ProductFileNumber)
Input #ProductFileNumber, Product_barcodenumber, Product_posdescription, Product_salelocation, Product_agerestricted, Product_agelimit, Product_pricetype, Product_retailprice, Product_vatcode, Product_print_guarantee_message, Product_print_guarantee_code, Product_DisplayMessage, Product_DisplayMessage_code, Product_sendtoppr, Product_requestserial, Product_ItemNotAllowed, Product_ItemNotAllowedReason, Product_RestrictProductQty, Product_RestrictProductAllowed, Product_DiscountNotAllowed, Product_RefundNotAllowed, Product_AskQtyBeforeSelling, Product_HelhtlyStartVoucherOK
If Trim(BarcodeNumber) = Trim(Product_barcodenumber) Then
ProductFound = 1
CloseAllFiles
Exit Do
End If
Loop
CloseAllFiles
Select Case ProductFound
Case 0 ' Nothing Found
CloseAllFiles
ItemNotFound(BarcodeNumber)
Case 1 'Item was found in data file
Select Case Product_ItemNotAllowed
Case 1 'Item is NOT allowed to be sold
ItemNotallowedScreen
Case 0 'Item is allowed to be sold
CloseAllFiles
Dim LocalProduct As String = Product_posdescription
Product_posdescription = strReplace(LocalProduct, "''", """")
SaleLocationNumber = Trim(Product_salelocation)
PriceType = Trim(Product_pricetype)
PriceCheck = Val(Trim(Product_retailprice))
PriceCheck =(PriceCheck * 100)
Select Case Product_agerestricted
Case 0
PriceTypeCheck 'Normal Item
Case 1
AgeLimitDisplay(Val(Trim(Product_agelimit))) 'Age limited Item
End Select
Exit Sub
End Select
End Select
Exit Sub
End If
Re: CSV to fixlength data files
Your code doesn't compile, and of course, it wouldn't run without at least a test csv file. If I understand right, you are running this code on a device that understands only DOS. Is that correct?
Does that DOS device get the csv from a computer running a 32- or 64-bit Windows version? Translating csv to fixed size is very easy on Windows, but of course, it would be helpful if you posted a test version of that csv file.
Does that DOS device get the csv from a computer running a 32- or 64-bit Windows version? Translating csv to fixed size is very easy on Windows, but of course, it would be helpful if you posted a test version of that csv file.
Re: CSV to fixlength data files
Oh sorry it complies on my end but then it had the other functions.
I am generating the csv file from a vb.net program running on a windows 64bit machine.
My plan was to import the csv file to a database file so it could run quicker (almost like a sql database)
I am generating the csv file from a vb.net program running on a windows 64bit machine.
My plan was to import the csv file to a database file so it could run quicker (almost like a sql database)
Re: CSV to fixlength data files
But you run the "final" application on DOS?
Re: CSV to fixlength data files
Yes sorry the acual program will be running on FreeDOS
example of the CSV File
the record are made up of the following feilds
something like this
With ProductData
Dim Prouctbarcode as string * 13
Dim ProductDescription as string * 30
etc...
end with
example of the CSV File
Code: Select all
00000983,"SWEETS",0012,0,-,S,189,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
00001009,"WHITE ENVELOPE",0032,0,-,S,010,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
00001019,"STAMP 2ND CLASS",0010,0,-,S,050,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
00001029,"ZERO VAT",0004,0,-,O,-,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
000433025057,"RICHMOND KS",0008,1,10,S,749,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
00872331,"CAVA",0005,1,10,S,1299,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
011210115255,"TABASCO SAUCE 57ML",0004,0,-,S,249,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
018341751017,"BAREFOOT CAB SAUV",0005,1,10,S,699,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
018341751024,"BAREFOOT SAUV BLANC",0005,1,10,S,699,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
018341751055,"BAREFOOT CHARDONY",0005,1,10,S,699,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
018341751062,"BAREFOOT ROSE",0005,1,10,S,699,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
018341751109,"BAREFOOT MERLOT",0005,1,10,S,699,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
018341751130,"B",0005,1,10,S,699,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
024000001669,"DEL MONTE SLICED PINEAPPLE 136",0011,0,-,S,130,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
024000001966,"SLICED PINEAPPLE",0018,0,-,S,179,A,0,-,0,-,0,0,0,-,0,-,0,0,0,0
- barcodenumber,
posdescription,
salelocation,
agerestricted,
agelimit,
pricetype,
retailprice,
vatcode,
print_guarantee_message,
print_guarantee_code,
displaymessage,
messagenumber,
sendtoppr,
requestserial,
itemnotallowed,
itemnotallowed_reason,
restrict_product_qty,
product_qty_allowed,
discount_not_allowed,
no_refund_allowed,
ask_for_qty_before_selling,
healthy_start_voucher_ok
something like this
With ProductData
Dim Prouctbarcode as string * 13
Dim ProductDescription as string * 30
etc...
end with
Re: CSV to fixlength data files
It is obvious, looking at the CSV data, that a fixed length file:
You've stated, that you are paying NCR 150.-- for absolutely outdated HW.
My advice would be: pay them 10.-- and spend the saved 140.-- for a SSD.
(If I'd take such old stuff, the people would have to pay me, to take it!)
- 1) must contain a fixed lenght string (padded if necessary)
2) size must equal longest string (in the whole CSV)
3) not certain, that this would speed things up
You've stated, that you are paying NCR 150.-- for absolutely outdated HW.
My advice would be: pay them 10.-- and spend the saved 140.-- for a SSD.
(If I'd take such old stuff, the people would have to pay me, to take it!)
Re: CSV to fixlength data files
Not related to the performance issues I think, but 'CloseAllFiles' seems to be called more then necessary.
When 'open failed', 'before exit do, and 'after do...loop'.
Why is the age limit for a "BAREFOOT CHARDONY" 10 years? :-)
I wonder if a 'line input' (with splitting and trimming afterwards) if faster then 'input'. I'll try to make some test code...
Edit: First test: no significant difference.
Not so easy to test here on Linux, the OS caches the disk. Probably FreeDOS does not?
Alternative: Read whole file in one 'blob' (or large chunks), process this 'blob' in memory... How big can these product csv-files be?
Edit: I need to create a much bigger csv-file first...
When 'open failed', 'before exit do, and 'after do...loop'.
Why is the age limit for a "BAREFOOT CHARDONY" 10 years? :-)
I wonder if a 'line input' (with splitting and trimming afterwards) if faster then 'input'. I'll try to make some test code...
Edit: First test: no significant difference.
Not so easy to test here on Linux, the OS caches the disk. Probably FreeDOS does not?
Alternative: Read whole file in one 'blob' (or large chunks), process this 'blob' in memory... How big can these product csv-files be?
Edit: I need to create a much bigger csv-file first...
Re: CSV to fixlength data files
It's probably HEX, therefore, a more sensible 16 years old!badidea wrote:Why is the age limit for a "BAREFOOT CHARDONY" 10 years? :-)
-
- Posts: 1002
- Joined: Jul 14, 2005 23:41
Re: CSV to fixlength data files
If you are using DOS and want fixed length, then why not use a dBase III file format. There is probably a FreeBASIC translation somewhere here on the forum or you can translate the one from Ethan Winer over at http://ethanwiner.com/fullmoon.html
Re: CSV to fixlength data files
The age limit is not 10 that converts to 18 on the PoS as other settings before it are
E
U
PG
12
16
18
21
Etc etc
I have 512mb ram and I’ve made 150mb as a RAM disk for firing the data files.
I know I need fixed length I’m just asking if would be worth processing the csv file at start up and create a
Sub function that creates a fixed width data file for reading from (speed)
I’m open to other ideas to speed up the database as at the moment when I sell a item I know that is in the system I get “item not found”
E
U
PG
12
16
18
21
Etc etc
I have 512mb ram and I’ve made 150mb as a RAM disk for firing the data files.
I know I need fixed length I’m just asking if would be worth processing the csv file at start up and create a
Sub function that creates a fixed width data file for reading from (speed)
I’m open to other ideas to speed up the database as at the moment when I sell a item I know that is in the system I get “item not found”
Re: CSV to fixlength data files
You obviously, didn't understand jj2007, when he wrote, pre-process on the Host system,Gablea wrote:I know I need fixed length I’m just asking if would be worth processing the csv file at start up and create a Sub function that creates a fixed width data file for reading from (speed)
before copying to the till ... (the fixed length file, NOT the CSV).
Re: CSV to fixlength data files
The files can be upto 30,000 products in them (I have customers running my windows version and they have 30,000 products and that is not struggling at all)badidea wrote:Alternative: Read whole file in one 'blob' (or large chunks), process this 'blob' in memory... How big can these product csv-files be?
Edit: I need to create a much bigger csv-file first...
This program is being developed on windows 2000.
Maybe I should just make a VB6 version of the bloody application and talk to mysql via the OdBC connection
Re: CSV to fixlength data files
I have tried that meany times but vb.net does not generate a file that would work with The FreeBASIC application (when I had the VB6 version it worked perfectly every time)MrSwiss wrote:You obviously, didn't understand jj2007, when he wrote, pre-process on the Host system,Gablea wrote:I know I need fixed length I’m just asking if would be worth processing the csv file at start up and create a Sub function that creates a fixed width data file for reading from (speed)
before copying to the till ... (the fixed length file, NOT the CSV).
So I’m now stuck just a csv file (unless someone know a way to get vb.net to output propper formatted fixed length data files)