TXT file auto import to csv

[kwhelchel]

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

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)