CSV to fixlength data files

General FreeBASIC programming questions.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

CSV to fixlength data files

Post by Gablea »

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)
jj2007
Posts: 2326
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: CSV to fixlength data files

Post by jj2007 »

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)
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.

Maybe you should check your loading routine instead of going for a fixed size solution?
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: CSV to fixlength data files

Post by Gablea »

jj2007 wrote:Maybe you should check your loading routine instead of going for a fixed size solution?
This is my function

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
jj2007
Posts: 2326
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: CSV to fixlength data files

Post by jj2007 »

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.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: CSV to fixlength data files

Post by Gablea »

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)
jj2007
Posts: 2326
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: CSV to fixlength data files

Post by jj2007 »

But you run the "final" application on DOS?
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: CSV to fixlength data files

Post by Gablea »

Yes sorry the acual program will be running on FreeDOS

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
the record are made up of the following feilds
  • 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
I know I can not use MySQL on dos and I know DOS does not have any offical Databases but I have read a while ago (but can not find it) how to make a fixed size data file and that seemed to run very quickly when i was experimenting with Windows / DOS options

something like this


With ProductData
Dim Prouctbarcode as string * 13
Dim ProductDescription as string * 30
etc...
end with
MrSwiss
Posts: 3910
Joined: Jun 02, 2013 9:27
Location: Switzerland

Re: CSV to fixlength data files

Post by MrSwiss »

It is obvious, looking at the CSV data, that a fixed length file:
  • 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
A probably better solution would be: SSD instead of slow HDD.

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!)
badidea
Posts: 2586
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: CSV to fixlength data files

Post by badidea »

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...
MrSwiss
Posts: 3910
Joined: Jun 02, 2013 9:27
Location: Switzerland

Re: CSV to fixlength data files

Post by MrSwiss »

badidea wrote:Why is the age limit for a "BAREFOOT CHARDONY" 10 years? :-)
It's probably HEX, therefore, a more sensible 16 years old!
PaulSquires
Posts: 999
Joined: Jul 14, 2005 23:41

Re: CSV to fixlength data files

Post by PaulSquires »

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
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: CSV to fixlength data files

Post by Gablea »

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”
MrSwiss
Posts: 3910
Joined: Jun 02, 2013 9:27
Location: Switzerland

Re: CSV to fixlength data files

Post by MrSwiss »

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)
You obviously, didn't understand jj2007, when he wrote, pre-process on the Host system,
before copying to the till ... (the fixed length file, NOT the CSV).
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: CSV to fixlength data files

Post by Gablea »

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...
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)

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
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: CSV to fixlength data files

Post by Gablea »

MrSwiss wrote:
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)
You obviously, didn't understand jj2007, when he wrote, pre-process on the Host system,
before copying to the till ... (the fixed length file, NOT the CSV).
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)

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)
Post Reply