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

Question

Question

Gaolai Peng · Sep 18, 2017

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

Vitaliy Serdtsev · Sep 19, 2017

Import data programmatically:

%SQL.ExImData
CSV()

There a sample of the code is brought.

1 0

Robert Cemper · Sep 20, 2017

try

Do ##class(%SQL.Util.Procedures).CSVTOCLASS(2, .rowType, pFileName, pDelimiter,,1)
;; no defaults if not required or the EXACT count: 9 params = 8 comma
;; I counted 9

and/or

Property Name As %String (MAXLEN="");

and/or

;; the last comma in rowType looks also suspect to me.

set rowType=" . . . . ,ActiveStatus Boolean"

HTH

0 0

Marco Blom · Dec 15, 2017

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.

0 0

score 2 · Answer 1 · 2017-09-20T03:28:00-04:00

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)}

score 0 · Answer 2 · 2017-09-20T10:31:23-04:00

What if I have extra properties such as InsertTime in the Test.cvs class?

What about them? When you call %SQL.Util.Procedures:CSVTOCLASS you need to specify rowtype, corresponding to your CSV structure. If you have InsertTime property you need to reflect it and any other properties CSV has in your rowtype.

score 1 · Answer 3 · 2017-12-14T12:30:21-05:00

Try

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

And check new Test.CSV class for import methods.

score 1 · Answer 4 · 2017-09-19T07:03:15-04:00

I prefer %SQL.Util.Procedures because it is easier to use and it generates classes and methods that can be easily modified.

score 1 · Answer 5 · 2017-12-15T09:15:03-05:00

You shouldn't create TestCsv.Csv class. CSVTOCLASS method would create the class you specified as a last argument automatically. After that you need to open that new class, it would have an Import method. Call it with your file to actually import data.

score 0 · Answer 6 · 2017-09-19T23:28:04-04:00

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,

score 0 · Answer 7 · 2017-09-20T09:55:38-04:00

Gaolai Peng · Sep 20, 2017

Thanks Lebedyuk, that's very helpful. What if I have extra properties such as InsertTime in the Test.cvs class?

0 0

score 0 · Answer 8 · 2017-09-20T17:42:45-04:00

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,

score 0 · Answer 9 · 2017-12-14T10:05:34-05:00

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!

score 1 · Answer 10 · 2017-12-15T09:36:41-05:00

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:

delete all previously created classes: ZenImport.Country, TestCsv.Csv, etc.

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, .rowtype, filename,";",,,"Test.CSV")

run in Portal:
```
select * from Test.CSV
```
open and see the class "Test.CSV". Profit!!!