Parse txt file

Question

Parse txt file

Viewing 15 posts - 31 through 45 (of 55 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1002109 More actions · Answer 1

Bruin wrote:

Is there some magic that pulls in the file?
Thx.

Not really.... just do a BULK INSERT from the file. I haven't had the time to post it but you should have no issue. Make a temporary target table should have a RowNum columns as and identity and a 500 character "DataFromFile" column. Then just change the code to read from that Temp table instead of the VALUES() clause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCommitted Points: 1925 More actions · Answer 2

I put the data in a Table now via bulk Insert, but the query now is failing...

Msg 537, Level 16, State 3, Line 5

Invalid length parameter passed to the LEFT or SUBSTRING function.

It processes about 3 records then errors.

  WITH 
cteGroup AS
(--==== First, we have to group the lines from the file. (THIS IS THE KEY TO EVERYTHING!)
 SELECT  df.PSFOrder_Id
        ,DataGroup    = COUNT(CASE 
                              WHEN substring(df.PSFile,1,2) = '/*'
                              THEN 1 
                               END) OVER (ORDER BY df.PSFOrder_Id)
        ,DataFromFile = df.PSFile
   FROM dbastuff.dbo.PSFileOrder df
)
,cteLeftTag AS
(
 SELECT  DataGroup 
        ,Tag1  = SUBSTRING(DataFromFile,1,CHARINDEX(':',DataFromFile)-1)
        ,Data1 = SUBSTRING(DataFromFile,CHARINDEX(':',DataFromFile)+2,500)
   FROM cteGroup
  WHERE DataFromFile > ''
    AND LEFT(DataFromFile,2)<>'/*'
)
 SELECT  DataGroup
        ,insert_job   = RTRIM(MAX(CASE WHEN Tag1 = 'insert_job' 
                                       THEN SUBSTRING(Data1,1,ISNULL(ca.JtPos-1,500))
                                       ELSE '' END))
        ,job_type     = RTRIM(MAX(CASE WHEN Tag1 = 'insert_job' AND ca.JtPos > 0
                                       THEN SUBSTRING(Data1,ca.JtPos+10,500)
                                       ELSE '' END))
        ,job_type     = RTRIM(MAX(CASE WHEN Tag1 = 'job_type'     THEN DATA1 ELSE '' END))
        ,days_of_week = RTRIM(MAX(CASE WHEN Tag1 = 'days_of_week' THEN DATA1 ELSE '' END))
        ,start_times  = RTRIM(MAX(CASE WHEN Tag1 = 'start_times'  THEN DATA1 ELSE '' END))
        ,run_calendar = RTRIM(MAX(CASE WHEN Tag1 = 'run_calendar' THEN DATA1 ELSE '' END))
        ,start_mins   = RTRIM(MAX(CASE WHEN Tag1 = 'start_mins'   THEN DATA1 ELSE '' END))
        ,machine      = RTRIM(MAX(CASE WHEN Tag1 = 'machine'      THEN DATA1 ELSE '' END))
        ,watch_file   = RTRIM(MAX(CASE WHEN Tag1 = 'watch_file'   THEN DATA1 ELSE '' END))
        ,box_name     = RTRIM(MAX(CASE WHEN Tag1 = 'box_name'     THEN DATA1 ELSE '' END))
   FROM cteLeftTag
  CROSS APPLY (VALUES(NULLIF(CHARINDEX('job_type',Data1),0)))ca(JtPos)
  GROUP BY DataGroup
;

Jeff Moden SSC Guru Points: 1002109 More actions · Answer 3

Bruin wrote:

I put the data in a Table now via bulk Insert, but the query now is failing...
Msg 537, Level 16, State 3, Line 5 Invalid length parameter passed to the LEFT or SUBSTRING function.
It processes about 3 records then errors.

There's pretty much no way that I can troubleshoot that from here except to tell you that there's something in the file that you loaded that doesn't look like what you said it would or you've made an error in how you loaded the data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCommitted Points: 1925 More actions · Answer 4

I just did a Bulk Insert, any way to try and debug?

Thx.

Jeff Moden SSC Guru Points: 1002109 More actions · Answer 5

If you were paying any of us $250 an hour, would you waste our time in such a fashion? 😉

We need to see the stuff that supports what you say just failed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCommitted Points: 1925 More actions · Answer 6

When I run this section it groups the data as expected...

SELECT df.PSFOrder_Id

,DataGroup = COUNT(CASE

WHEN substring(df.PSFile,1,2) = '/*'

THEN 1

END) OVER (ORDER BY df.PSFOrder_Id)

,DataFromFile = df.PSFile

FROM dbastuff.dbo.PSFileOrder df

<><><> It seems to be failing <><><>

,cteLeftTag AS

(

SELECT DataGroup

,Tag1 = SUBSTRING(DataFromFile,1,CHARINDEX(':',DataFromFile)-1)

,Data1 = SUBSTRING(DataFromFile,CHARINDEX(':',DataFromFile)+2,500)

FROM cteGroup

WHERE DataFromFile > ''

AND LEFT(DataFromFile,2)<>'/*'

)

SELECT DataGroup

,insert_job = RTRIM(MAX(CASE WHEN Tag1 = 'insert_job'

THEN SUBSTRING(Data1,1,ISNULL(ca.JtPos-1,500))

ELSE '' END))

,job_type = RTRIM(MAX(CASE WHEN Tag1 = 'insert_job' AND ca.JtPos > 0

THEN SUBSTRING(Data1,ca.JtPos+10,500)

ELSE '' END))

,job_type = RTRIM(MAX(CASE WHEN Tag1 = 'job_type' THEN DATA1 ELSE '' END))

,days_of_week = RTRIM(MAX(CASE WHEN Tag1 = 'days_of_week' THEN DATA1 ELSE '' END))

,start_times = RTRIM(MAX(CASE WHEN Tag1 = 'start_times' THEN DATA1 ELSE '' END))

,run_calendar = RTRIM(MAX(CASE WHEN Tag1 = 'run_calendar' THEN DATA1 ELSE '' END))

,start_mins = RTRIM(MAX(CASE WHEN Tag1 = 'start_mins' THEN DATA1 ELSE '' END))

,machine = RTRIM(MAX(CASE WHEN Tag1 = 'machine' THEN DATA1 ELSE '' END))

,watch_file = RTRIM(MAX(CASE WHEN Tag1 = 'watch_file' THEN DATA1 ELSE '' END))

,box_name = RTRIM(MAX(CASE WHEN Tag1 = 'box_name' THEN DATA1 ELSE '' END))

FROM cteLeftTag

CROSS APPLY (VALUES(NULLIF(CHARINDEX('job_type',Data1),0)))ca(JtPos)

GROUP BY DataGroup

;

Jeff Moden SSC Guru Points: 1002109 More actions · Answer 7

I don't know what the data is that you're playing that code against. Is strongly suspect that you may have imported the data incorrectly OR there's an anomaly in the data that you didn't expect.

Since you said previously that you can't show actual data, I suggested that you post the code you used to import the data with and you've resisted that. It would also be good to see the CREATE Table statement because there's a key element to what I did in my demo... the rows are numbered to preserve the order. That's why I was talking about all this needing to be single threaded... it MUST be done in a serial fashion.

Without the data that's failing and without the BULK INSERT code and without the DDL for the target of the BULK INSERT, all I can tell you is to go look at the actual data and find the anomaly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCommitted Points: 1925 More actions · Answer 8

CREATE TABLE [dbo].[PSFileOrder](
[PSFOrder_Id] [int] IDENTITY(0,1) NOT NULL,
[PSFile] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK_PSFileOrder] PRIMARY KEY CLUSTERED 
(
[PSFOrder_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Jeff Moden SSC Guru Points: 1002109 More actions · Answer 9

Table looks right but I don't know why you resist providing the information I asked for. You didn't include the BULK INSERT I asked for. 🙁

No matter though. I made the mistake of thinking that you actually copied my code and used it. You have not. If we look at the first section of you code posted above, we see the following...

SELECT df.PSFOrder_Id
,DataGroup = COUNT(CASE
WHEN substring(df.PSFile,1,2) = '/*'
THEN 1
END) OVER (ORDER BY df.PSFOrder_Id)
,DataFromFile = df.PSFile

Here's my original working code....

SELECT  df.RowNum                                                                    
       ,DataGroup    = COUNT(CASE                                                    
                             WHEN LEFT(df.DataFromFile,2) = '/*'                     
                             THEN 1                                                  
                              END) OVER (ORDER BY df.RowNum ROWS UNBOUNDED PRECEDING)
       ,DataFromFile = CONVERT(VARCHAR(500),df.DataFromFile)

With the obvious required change of column names being an understood exception, do you see any differences that just might make a wee bit of a difference? And, no... I'm not going to spend any more time desk-checking the rest of your code.

Sorry, Bruin, but you managed to piss off one the most patient people on the planet.. and he's all done trying to help here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCommitted Points: 1925 More actions · Answer 10

I apologize Jeff, I really appreciate the time you take to help solve issues.

This is the code to load the file...

Many thanks, and hope you don't go dark on this...

Again thanks..

#LoadPSFile
cls
Get-content "C:\FileLoading\jobs.txt" | 
Foreach-Object {$_ -replace "'", "''"} |
ForEach-Object{invoke-sqlcmd -serverInstance localhost\sqlexpress -query "Insert dbastuff.dbo.PSFileOrder(PSFile) Values ('$_')"}

frederico_fonseca SSCoach Points: 15583 More actions · Answer 11

you really have no idea of what you are doing neither do you follow the advise of this forum experts. on this case you decided to change the supplied code by yourself and then complain it doesn't work.

it really is a waste of our time to try and help you.

first thing you need to do is to use BULK INSERT to load the file - not that horrible snippet you have above (DO NOT EVER DO IT THAT WAY!!!)

first read the documentation of BCP here https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16

you have one example you can use here https://stackoverflow.com/questions/6033275/bcp-to-bulk-insert-into-a-single-row-field

I also advise you strongly to read the documentation of the sqlclient bulkcopy - (google for powershell sql bulkcopy) as this will be another option you can use with more options

once you have the data loaded using BCP and after making sure that the file got loaded CORRECTLY and has each input line on a single row on the destination table and that the rows are on the SAME order as they are on the input file go and use the EXACT code that Jeff gave you and see if you still have issues.

Bruin SSCommitted Points: 1925 More actions · Answer 12

Okay I have the file loaded using BCP. I created a format file and used BCP provided... all rows loaded

All data is now loaded to the table I used above. I used Jeff's code as he previously provided and just substituted the new table and field names.. to the top part of the SP.

This is the error:

Msg 537, Level 16, State 2, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

I did notice in example provided that job_type is there twice... I know that insert_job and job_type are contained on the same line so I can see the 2 selections\lookups, but was unsure of the extra job_type in the example.

THanks again and I hope I have provided everything needed...

SELECT  DataGroup
        ,insert_job   = RTRIM(MAX(CASE WHEN Tag1 = 'insert_job' 
                                       THEN SUBSTRING(Data1,1,ISNULL(ca.JtPos-1,500))
                                       ELSE '' END))
        ,job_type     = RTRIM(MAX(CASE WHEN Tag1 = 'insert_job' AND ca.JtPos > 0
                                       THEN SUBSTRING(Data1,ca.JtPos+10,500)
                                       ELSE '' END))
        ,job_type     = RTRIM(MAX(CASE WHEN Tag1 = 'job_type'     THEN DATA1 ELSE '' END))

  WITH 
cteGroup AS
(--==== First, we have to group the lines from the file. (THIS IS THE KEY TO EVERYTHING!)
 SELECT  df.PSFOrder_Id
        ,DataGroup    = COUNT(CASE 
                              WHEN LEFT(df.PSFile,2) = '/*'
                              THEN 1 
                               END) OVER (ORDER BY df.PSFOrder_Id ROWS UNBOUNDED PRECEDING)
        ,DataFromFile = CONVERT(VARCHAR(500),df.PSFile)
   FROM PSFileOrder df

bcp copy2.dbo.psfileorder in c:\ftp_in\jobs.txt -f c:\ftp_in\fileorder.fmt -S local\SQLEXPRESS -E -T

frederico_fonseca SSCoach Points: 15583 More actions · Answer 13

based on the code by Jeff the only way you get that error is if your file contains a line that has data and does not have a ":" or if the lines with "/*" do not have this on the very first position.

As you refuse to supply the real file there is nothing we can do - plenty you can and should do - including starting processing just part of the records until you identify the line that is causing the issue.

Bruin SSCommitted Points: 1925 More actions · Answer 14

I stated in early post I couldn't supply file but fill check table as you gave a good starting place... What about job_type in there twice?

Thx.

Jeff Moden SSC Guru Points: 1002109 More actions · Answer 15

Bruin wrote:

I stated in early post I couldn't supply file but fill check table as you gave a good starting place... What about job_type in there twice?
Thx.

Check the code. Do you think having such duplication will work?

Also, you may not be able to post the exact data but nothing is preventing you from knocking out some sanitized test data. If you want help, you have to be willing to be helped.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)