Ask Your Question
0

How do I export a base table to csv in macro. [closed]

asked 2014-02-25 10:50:19 +0200

dfu gravatar image

updated 2015-09-10 21:23:08 +0200

Alex Kemp gravatar image

I have googled and found the select * into text "filename" from "table". But no easy/simple way of running it in a macro I have tried several examples but they make no sense to me as I'm not great with coding. Once the macro works I going to assign it to a button on a form if that makes any difference. I'm running LibreOffice Base 4.1 running on Windows 7.

Thanks in advance

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 08:00:13.983140

Comments

If, instead, you just want to run the command from the Tools > SQL menu, see here

Alex Thurgood gravatar imageAlex Thurgood ( 2014-08-11 09:51:01 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2014-03-16 04:22:12 +0200

skidmog gravatar image

updated 2014-03-16 04:26:07 +0200

One of my exercises was to export all of the tool information to load into a special query in our ERP to determine when the tools have been used if ever. So I did the query and wrote to csv file. You'll notice some extraneous redundant code but I tried several things that I left behind. 2 export examples below - the second one I was creating labels and exported to CSV and then opened Excel and prepared the file for a Word Doc mail-merge.... I haven't done that in Calc or Writer if possible, but this worked to print labels.

Sub ERP_JOBMOUsed_out()

' Purpose: to export the Query JOBMOUsed to a text file convert to XLS and ready to import to Global Shop
'  to table X_JOB_WUSEDMO for crystal report to identify tools used and not used in jobs in history
Dim oSvc as object 
Dim sROUTER, sWORKGROUP, sASSYCATEGORY, sAssyID, sJobID, sJobName, sJobMO as string


'''' write transaction to text file for ERP import inventory issue
Dim sOutFilename as string
Dim nOutFileNum as integer
Dim sOutRileRoot, sOutDataLine as string
sOutFileRoot = ConvertFromURL(getAppRoot)


sOutFilename =  sOutFileRoot & "appstuff\invty\ERPImports\TOOLDB-JobMOUsed.csv"

nOutFileNum = FreeFile()
' Open output file and write data   
Open sOutFilename For Output Access Read Write As #nOutFileNum
    sOutDataLine = "ROUTER, WORKGROUP, ASSYCATEGORY, AssyID, JobID, JobName, JobMO "
    ' Write heading line to csv
    Print #nOutFileNum, sOutDataLine



        '   sql = "SELECT * FROM `JOBMOUsed` Where assyID <> 0 "
        '   sql = "SELECT * "
    sql = "SELECT `whereused`.`AssyCategory`, "
    sql = sql + "`whereused`.`AssyID`, "
    sql = sql + "`whereused`.`JobID`, "
    sql = sql + "`machine_jobs`.`JobName`, "
    sql = sql + "`machine_jobs`.`MO` AS `JobMO`, "
    sql = sql + "`assembly_category`.`categoryName` "
    sql = sql + " FROM `whereused` AS `whereused`, `machine_jobs` AS `machine_jobs`, `assembly_category` AS `assembly_category` "
    sql = sql + " WHERE `whereused`.`JobID` = `machine_jobs`.`JobID` AND `whereused`.`AssyCategory` = `assembly_category`.`categoryID` "

    Result = SQLquery(sql)
        Result.next()

while Result.next()
        sASSYCATEGORY = Result.getString(1)
        sAssyID = Result.getString(2)
        sJobID = Result.getString(3)
        sJobName = Replace(Result.getString(4), ",", " ")
            sJobName = Replace(sJobName, Chr(34), " ")
        sJobMO = Result.getString(5)
        if Len(sJobMO) < 5 then 
            sROUTER = STRING(5 - Len(sJobMO), "0")  & Result.getString(5)
                '   sROUTER = String( 5 - Len(sJobMO), Chr(48))
          else 
              sROUTER = sJobMO
        end if
        sWORKGROUP = Result.getString(6) 

        sOutDataLine = "*" &s & "*" & "," & sPart &","& sDesc1 &","& sCabDwrPos &","& sMfg &","& sMfg_Part 
        sOutDataLine = Chr(34) & sROUTER & Chr(34) & "," & sWORKGROUP & "," & sASSYCATEGORY & "," & sAssyID & "," & sJobID & "," & sJobName & "," & sJobMO 
        ' Write detail line to csv
        Print #nOutFileNum, sOutDataLine

wend

SQLclose()

close #nOutFileNum

' took out the excel conversion - GSS GAB is working with CSV file
'Dim sOutFilexls as string
'
'sOutFilexls = sOutFileRoot & "appstuff\invty\ERPImports\TOOLDB-JobMOUsed.xls"
'if fileexists ...
(more)
edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-02-25 10:50:19 +0200

Seen: 1,435 times

Last updated: Mar 16 '14