TXT file auto import to csv

[RusselB]

Hello there,
I am looking for a way to auto import and convert a text file to csv. I can do it manually with no problem but I need to do this behind the scenes to help a client out.
And suggestions or code would be great. This will be done on a scheduled format for everyday.

Thanks
Keith

[Villeroy]

Write a little program. This has nothing to do with any office suite.

[Villeroy]

This is how you import that file with a satisfactory result:
Encoding: UTF-8
Language: anything English
Skip first 12 rows
Column delimiter: |

A macro template which imports this file or multiple files of the same kind: viewtopic.php?f=21&t=77069&p=351686#p351686
Open template, enter 124,0,76,13,,3081,false,true,true,false,false into the FilterOptions cell, enter the file name or a name pattern into the pattern cell and save the file in the same directory. Click the [Go] button.

[MrProgrammer]

Hi, and welcome to the forum.

I am looking for a way to auto import and convert a text file to csv.
ATTACHMENTS: eod.txt

Write a little program. This has nothing to do with any office suite.

Agreed. Here's a little program for sed which converts your TXT file to CSV.

$ sed -e '/%/!d;s/^ *//;s/[ [:cntrl:]]*$//;s/  */,/g'  eod.txt >eod.csv
$ sed -e '/|/!d;/^[- ]/d;s/[[:cntrl:]]//g;s/ *| */,/g' eod.txt >>eod.csv
$ cat eod.csv
Department,Qty,TotSales,NetSales,Taxable,Accntable,%
BEVERAGES,23,60.30,60.30,40.80,64.18,2.13
BREAKFAST,120,834.60,761.06,761.06,833.36,29.43
CATERING OUT,1,36.00,36.00,36.00,39.42,1.27
COFFEE,60,189.00,184.05,184.05,201.53,6.66
CORP. CATERING,60,451.80,451.80,451.80,494.72,15.93
DELIVERY,1,14.95,14.95,0.00,14.95,0.53
DESSERT,2,11.90,11.90,11.90,13.03,0.42
ENTREES,16,108.65,64.70,64.70,70.85,3.83
FRESH JUICE,14,97.55,96.66,96.66,105.84,3.44
KIDS,10,48.65,48.65,48.65,53.27,1.72
PASTAS,1,15.50,15.50,15.50,16.97,0.55
PASTRIES,6,19.70,19.70,19.70,21.57,0.69
RETAIL,1,1.50,1.50,1.50,1.64,0.05
SALAD,66,423.60,392.40,392.40,429.68,14.94
SANDWICH,34,250.25,228.08,228.08,249.75,8.82
SIDE ORDERS,41,161.40,152.76,152.76,167.27,5.69
SMOOTHIES,6,35.70,35.70,35.70,39.09,1.26
SOUPS,9,26.45,26.45,26.45,28.96,0.93
SPECIALS,3,28.50,28.50,28.50,31.21,1.01
TEA,3,12.85,12.11,12.11,13.26,0.45
WINE GL,1,6.95,6.95,6.95,7.61,0.25

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[JeJe]

Modifying some code I just used in another thread... totally custom sub which assumes first entry of table always "BEVERAGES" and titles are always the same.

Open in a Writer document...

Code: Select all   Expand viewCollapse view



Sub test
   Dim p As String
   p1=thiscomponent.gettext.string

   a1 = instr(1,p1,"BEVERAGES")
   a2 = instr(a1,p1,"-"
   p1 = mid(p1,a1,a2-a1)

   dim b() as byte, c as long,gotpipe as boolean,firstspace as boolean
   b() = p1: c =-2

   for i = 0 to ubound(b) step 2
      if b(i) = 32 and b(i+1) = 0 then       ' if space
         if gotpipe =false then
            if firstspace = true then
               c= c+2
               b(c) =32
               b(c+1) =0
               firstspace = false
            end if
         end if


      elseif b(i) = 124 and b(i+1) = 0 then 'if pipe then change to comma
         firstspace = true
         gotpipe = true
         if b(c) = 32 and b(c +1) =0 then
         else
            c= c+2
         end if
         b(c) =44
         b(c+1) =0
      else 'otherwise copy character
         firstspace = true
         c= c+2
         b(c) =b(i)
         b(c+1) =b(i+1)

      end if

      if b(i) = 10 and b(i+1) = 0 then gotpipe =false
   next
   redim preserve b(c)
   p1= b

   p1 = "Department,Qty,TotSales,NetSales,Taxable,Accntable,%" & chr(10) & p1

   msgbox p1
End Sub




Edit: (earlier version was pulled as didn't quite work)