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
Import data programmatically:
There a sample of the code is brought.
Provided you have this csv:
You can import it into class Test.CSV:
1. Generate a persistent class Test.CSV
2. Import file or files
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:
If you have a tab separated file, you need to change Import method signature from:
to:
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.
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
Try
And check new Test.CSV class for import methods.
I prefer %SQL.Util.Procedures because it is easier to use and it generates classes and methods that can be easily modified.
Hi Eduard, hard to get it working!
my file:
my code:
{
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.
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.
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,
Thanks Lebedyuk, that's very helpful. What if I have extra properties such as InsertTime in the Test.cvs class?
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.
{
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:
open and see the class "Test.CSV". Profit!!!
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue