Question
· Sep 18, 2017

How to import a tab separated text file into a SQL table programmatically?

Can anyone provide some sample in ObjectScripts to import a tab separate txt file into a SQL table?  I checked the document, there are two available classes, %SQL.Import.Mgr and %SQL.Until.Procedures, which one is better choice?

Thanks

Discussion (13)2
Log in or sign up to continue

Provided you have this csv:

car,2000,100.51,27.10.2016,
phone,2003,65.8,15.01.2017,

You can import it into class Test.CSV:

1. Generate a persistent class Test.CSV

set rowtype = "name VARCHAR(50),year INTEGER,amount NUMERIC(9,2),date DATE"
set filename = "D:\data.csv"
do ##class(%SQL.Util.Procedures).CSVTOCLASS(2, .rowtype, filename,,,,"Test.CSV")

2. Import file or files

do ##class(Test.CSV).Import(2,filename)

 

Usually you can't import your CSV right away - the dates are in a different format, etc. You need to modify Import method and property definitions. For example I often:

  • Add FORMAT=4 property parameter for dates to import dates in dd/mm/yyyy format.
  • Find&replace Library.
  • Add this else line in Import method:
if $$$ISOK(tStatus) { set tCounter = tCounter + 1 } else { w $System.Status.GetErrorText(tStatus) return}

 

If you have a tab separated file, you need to change Import method signature from:

pDelimiter As %String = ","

to:

pDelimiter As %String = {$c(9)}

Hi Eduard, hard to get it working!

my file:

my code:

Class TestCsv.Csv Extends %Persistent
{

Property name As %String;

Property year As %Integer;

Property amount As %Numeric;

Property date As %Date;

ClassMethod Import()
{
set rowtype = "name VARCHAR(50),year INTEGER,amount NUMERIC(9,2),date DATE"
    set filename = "c:\temp\data.csv"
    do ##class(%SQL.Util.Procedures).CSVTOCLASS(2, .rowtype, filename,,,,"TestCsv.Csv")
}

My result:

(I use Windows 10)

no result

My filetest:

I still cannot find the error.

Can you provide me some sample code in ObjectScripts? Following sample code  is from the document, I don't really understand what is the purpose of the last three lines?

set rowtype = "StateFips VARCHAR(2),Zip_Code VARCHAR(5),State_Abbr VARCHAR(2),Name VARCHAR(200),Longitude_West Numeric(10,6),Latitude_North Numeric(10,6)"
set filename = "/Users/test/Documents/zip.csv"
set result = ##class(%SQL.Statement).%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.filename)
set resultSet = result.%NextResult()
write resultSet.%Next()
write resultSet.StateFips

Thanks,

 This is my sample code

Class CUSTOM.Util.Script.Temp.MyTest Extends %Persistent
{

Property CreatedOn As %TimeStamp [ InitialExpression = {$ZDT($H,3,1)} ];

Property RPPI As %String;

Property Name As %String;

Property BirthDate As %Date;

Property ProgramName As %String;

Property CaseTouchedWhen As %DateTime;

Property ActiveStatus As %Boolean;

Property Status As %String [ InitialExpression = "Pending" ];

Index idxRPPI On RPPI;

ClassMethod LoadFromFile()
{
  Set pFileName="C:\mytest.tsv"
 Set pDelimiter= $C(9)
 Set rowType="RPPI varchar(50),Name varchar(516),BirthDate Date,ProgramName varchar(50),CaseTouchedWhen DateTime,ActiveStatus Boolean,"
 Do ##class(%SQL.Util.Procedures).CSVTOCLASS(2, .rowType, pFileName, pDelimiter,,1,,,,)
}
}

And I run following command:

do ##class(CUSTOM.Util.Script.Temp.MyTest).LoadFromFile()

but I got following error

Error <PARAMETER>

Do you where is wrong in my code?

Thanks,
 

Hi there,

I have the following sample code, but none of them works.

with the last class I did test my csv file and it worked.

Class ZenImport.Country Extends %Persistent
{

Property Code As %String;

Property Name As %String;

ClassMethod LoadFromFile()
{
 Set pFileName="C:\temp\Country.csv"
 Set pDelimiter= $C(9)
 //Set pDelimiter=";"
 Set rowType="Code VARCHAR(2),Name VARCHAR(9)"
 Do ##class(%SQL.Util.Procedures).CSVTOCLASS(2,.rowType, pFileName, pDelimiter)
}

ClassMethod LoadFromFile2()
{
 set rowtype = "Code VARCHAR(2),Name VARCHAR(9)"
 set filename = "c:\temp\Country.csv"
 do ##class(%SQL.Util.Procedures).CSVTOCLASS(2, .rowtype, filename,,,,"Country.csv")
}

ClassMethod ImportCountries(srcDir As %String = "C:\temp", srcFile As %String = "Country.csv")
{
    try {
        set status = ##class(ZenImport.Country).%DeleteExtent()
        if $$$ISOK(status) {write !, "Deleted LS.Country Data"}
        else {do $system.Status.DisplayError(status) return}
    catch ex {}

    set srcStream = ##class(%Stream.FileCharacter).%New()
    set srcPath = srcDir_"\"_srcFile
    set srcStream.Filename = srcPath
    set srcStream.TranslateTable = "UTF8"

    set selectMode = 0
    set rowType = "Code VARCHAR(2),Name VARCHAR(9)"
    set delimiter = ";"
    set quote = """"
    set headerCount = 0
    set classname = "ZenImport.Country"
    do ##class(%SQL.Util.Procedures).CSVTOCLASS(selectMode, rowType, srcStream, delimiter, quote, headerCount, classname)

    write !, "Imported " _ srcPath _ " to ZenImport.Country", !
    QUIT
}

ClassMethod ImportStream() [ ZenMethod ]
{
///Test csv File -THIS ONE WORKS!-
Set stream=##class(%Stream.FileCharacter).%New()
Set sc=stream.LinkToFile("c:\temp\Country.csv")
if $$$ISERR(sc) Quit 
While 'stream.AtEnd {
Set line=stream.Read()

}

write !, line

Quit $$$OK
}

Test reading csv:

 

Hopefully you have a suggestion how I can get it working!

Hi Marco.

CSVTOCLASS creates a new class if it doesn't already exist and then it calls the Import method.

Since you already have created the class, it is not created, but there is no Import method, so nothing works.

Solution:

  1. delete all previously created classes: ZenImport.Country, TestCsv.Csv, etc.
  2. run in terminal:
    USER>set rowtype "Code VARCHAR(2),Name VARCHAR(9)"
    USER>set filename "c:\temp\Country.csv"
    USER>do ##class(%SQL.Util.Procedures).CSVTOCLASS(2, .rowtypefilename,";",,,"Test.CSV")
    
  3. run in Portal:
    select * from Test.CSV

    open and see the class "Test.CSV". Profit!!!