STK files info to MS SQL 2019 table
-
Hello,
I try to make file info import in MS SQL 2019 server and i cant find solution only txt, xml etc.
I have repeatable file structure like ISTK-x is one row. Files are multiple but they all start with ISTK-1 and so on.
[ISTK-1]
MAT=EG_U998_ST38_18_6
R=1
L=542
B=111
A=1
REF=38877_3
EDGE1=0.8mm abs
EDGE2=0.8mm abs
EDGE3=0.8mm abs
EDGE4=0.8mm abs
[ISTK-1]
[ISTK-2]
MAT=EG_U998_ST38_18_6
R=1
L=70
B=111
A=1
REF=38877_3
EDGE1=0.8mm abs
EDGE2=0.8mm abs
EDGE3=0.8mm abs
EDGE4=0.8mm abs
[ISTK-2]
[ISTK$FORM-1]
[ISTK$FORM-1]
[ISTK$FORM-2]
[ISTK$FORM-2]
[ISTKALG]
DATUM=160322
[ISTKALG]
[ISTKALG$FORM]
[ISTKALG$FORM]
I made table with these column values:
SELECT TOP (1000) [ISTK]
,[MAT]
,[R]
,[L]
,
,[A]
,[REF]
,[EDGE1]
,[EDGE2]
,[EDGE3]
,[EDGE4]
FROM [DemoDB02].[dbo].[STK]
Should i have like process to make STK file structure to XML on JSON first and then read those files and write in database or other options.
I am not developer only started to learn in udemy. Done some training courses.
If more info needed i can provide.
-
Sample file what i try to read or import.
-
BULK INSERT is maybe the option? Found this sample code.
BULK INSERT dbo.personlist
FROM 'c:\source\personlist.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';', --CSV field delimiter
ROWTERMINATOR = '\n', --Use to shift the control to next row
TABLOCK,
CODEPAGE = 'ACP'
);
select * from dbo.personlist;
-
Did you make any progress with this?
That file structure does not look easy to process. Your idea of converting the source data to JSON would certainly make it easier to handle. Converting to CSV would make it even easier and not incur the JSON file-bloating effect.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Yes still struggle to sort this out.
I have made in C# *STK file decoder. It makes table vien in windows form.
namespace STK
{
public class MyData
{
public MyData()
{
//empty constructor;
}
public MyData(string sFileName)
{
LoadData(sFileName);
}
public string ISTK { get; set; }
public string MAT { get; set; }
public int R { get; set; }
public int L { get; set; }
public int B { get; set; }
public int A { get; set; }
public string REF { get; set; }
public string BEM { get; set; }
public string BEM2 { get; set; }
public string EDGE1 { get; set; }
public string EDGE2 { get; set; }
public string EDGE3 { get; set; }
public string EDGE4 { get; set; }
public List<MyData> LoadData(string sFileName)
{
List<MyData> internallist = new List<MyData>();
MyData md = null;
List<string> lines = File.ReadAllLines(sFileName).Where(x => x.Trim().Length > 0).ToList();
string pattern = @"\[ISTK\-\d{1,}\]";
Regex r = new Regex(pattern);
foreach (string line in lines)
{
if (r.IsMatch(line))
{
md = new MyData() { ISTK = line.Replace("[", "").Replace("]", "") };
if (internallist.Where(x => x.ISTK.Equals(md.ISTK)).SingleOrDefault() == null) internallist.Add(md);
}
else
{
string[] props = line.Split('=');
switch (props[0])
{
case "MAT":
md.MAT = props[1];
break;
case "R":
md.R = Convert.ToInt32(props[1]);
break;
case "L":
md.L = Convert.ToInt32(props[1]);
break;
case "B":
md.B = Convert.ToInt32(props[1]);
break;
case "A":
md.A = Convert.ToInt32(props[1]);
break;
case "REF":
md.REF = props[1];
break;
case "BEM":
md.BEM = props[1];
break;
case "BEM2":
md.BEM2 = props[1];
break;
case "EDGE1":
md.EDGE1 = props[1];
break;
case "EDGE2":
md.EDGE2 = props[1];
break;
case "EDGE3":
md.EDGE3 = props[1];
break;
case "EDGE4":
md.EDGE4 = props[1];
break;
}
}
}
return internallist;
}
}
}It retuns this view:
Now i try to do same only to store in MS SQL DB for reports.
P.S. I make these tools to make my work more productive and to learn new exciting things.
-
Ok then i should chek how to parse a text file (*STK) and save to .csv or XML or JSON. Then read those files and write info in DB.
- ManiaKsLV wrote:
Ok then i should chek how to parse a text file (*STK) and save to .csv or XML or JSON. Then read those files and write info in DB.
The C# code provided above looks like it could be an enormous help. To get this working directly in SSIS, I'd use a Script Component Source in a dataflow. This would consume the source file and stream out rows of data in the desired format, which could be pushed directly to a database table.
But as you are 'not a developer', this is likely beyond what you can achieve right now. Instead, if you can get the file converted to CSV format, you'll have a much easier time importing it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Ok will check if i can understand Script Component Source thing.
-
So... i manage to add C# function to export decoded *STK file to *CSV file.
And imported in ms sql table...
File:
SQL:
Included *ZIP with stk decoder and generated files.
But still i will try find way to make that automated in SQL server i have all parts for result just don't know how to put them in right places.
-
An SSIS package can orchestrate the process for you.
Option 1
- Execute process task to call your C# executable with suitable arguments, to create the CSV
- Dataflow to import the CSV
Option 2
- Modify your C# code to work as a Script Component source
- Dataflow to perform the import directly, from Script Component source to target table
Option 3
- Modify your C# code to update the SQL Server table directly
The benefit of option 1 is that you've already got the C# written and working.
The benefit of option 2 is that all code is hosted within SQL Server & therefore you don't have to manage an additional separate codebase.
If you decide on option 3, you could simply call it using Windows Task Scheduler (no SSIS package is required).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply