CSV to fixlength data files

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

CSV to fixlength data files

Postby Gablea » Aug 10, 2018 23:21

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

Re: CSV to fixlength data files

Postby jj2007 » Aug 11, 2018 2:23

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

Re: CSV to fixlength data files

Postby Gablea » Aug 11, 2018 10:12

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

Re: CSV to fixlength data files

Postby jj2007 » Aug 11, 2018 13:40

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

Re: CSV to fixlength data files

Postby Gablea » Aug 11, 2018 18:19

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

Re: CSV to fixlength data files

Postby jj2007 » Aug 11, 2018 19:10

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

Re: CSV to fixlength data files

Postby Gablea » Aug 11, 2018 19:13

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: 2811
Joined: Jun 02, 2013 9:27
Location: Switzerland

Re: CSV to fixlength data files

Postby MrSwiss » Aug 11, 2018 19:42

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: 1008
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: CSV to fixlength data files

Postby badidea » Aug 11, 2018 21:13

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: 2811
Joined: Jun 02, 2013 9:27
Location: Switzerland

Re: CSV to fixlength data files

Postby MrSwiss » Aug 11, 2018 23:02

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: 710
Joined: Jul 14, 2005 23:41
Contact:

Re: CSV to fixlength data files

Postby PaulSquires » Aug 11, 2018 23:26

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

Re: CSV to fixlength data files

Postby Gablea » Aug 12, 2018 0:06

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: 2811
Joined: Jun 02, 2013 9:27
Location: Switzerland

Re: CSV to fixlength data files

Postby MrSwiss » Aug 12, 2018 0:14

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

Re: CSV to fixlength data files

Postby Gablea » Aug 12, 2018 0:21

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

Re: CSV to fixlength data files

Postby Gablea » Aug 12, 2018 0:24

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)

Return to “General”

Who is online

Users browsing this forum: Google [Bot] and 3 guests