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
TXT file auto import to csv
TXT file auto import to csv
- Attachments
-
- eod.txt
- (1.94 KiB) Downloaded 195 times
Last edited by RusselB on Tue Nov 12, 2019 9:13 pm, edited 1 time in total.
Reason: Moved from Calc forum based on details regarding the actual process requested.
Reason: Moved from Calc forum based on details regarding the actual process requested.
open office 4 windows 7, 10
Re: TXT file auto import to csv
Write a little program. This has nothing to do with any office suite.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: TXT file auto import to csv
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: TXT file auto import to csv
Hi, and welcome to the forum.
kwhelchel wrote:I am looking for a way to auto import and convert a text file to csv.
ATTACHMENTS: eod.txt
Agreed. Here's a little program for sed which converts your TXT file to CSV.Villeroy wrote:Write a little program. This has nothing to do with any office suite.
$ 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.25If 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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: TXT file auto import to csv
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...
Edit: (earlier version was pulled as didn't quite work)
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
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)