TXT file auto import to csv

Creating a macro - Writing a Script - Using the API

TXT file auto import to csv

Postby kwhelchel » Tue Nov 12, 2019 9:04 pm

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
Attachments
eod.txt
(1.94 KiB) Downloaded 11 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.
open office 4 windows 7, 10
kwhelchel
 
Posts: 1
Joined: Tue Nov 12, 2019 8:56 pm

Re: TXT file auto import to csv

Postby Villeroy » Tue Nov 12, 2019 11:50 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27375
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: TXT file auto import to csv

Postby Villeroy » Wed Nov 13, 2019 12:57 am

This is how you import that file with a satisfactory result:
Encoding: UTF-8
Language: anything English
Skip first 12 rows
Column delimiter: |
Bildschirmfoto von 2019-11-12 23-38-38.png


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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27375
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: TXT file auto import to csv

Postby MrProgrammer » Wed Nov 13, 2019 2:05 am

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
Villeroy wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3896
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: TXT file auto import to csv

Postby JeJe » Wed Nov 13, 2019 2:25 am

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)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 628
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 11 guests