SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


File import to table in Unix


File import to table in Unix

Author
Message
sql crazy kid
sql crazy kid
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 243
I am struck in one point as we gets files in unix box and we don't have any windows server .
Issue - We get pipe'|' separated text file, usually this can be bcp in to table if the structure exactly matching with table .
Here structure is same but some of the rows having issue with row delimiters which cause overlapping to next line.
This file opens in excel perfectly no issue.

I need to find a solution how to fix this. This we cannot say forrmat issue as this issue with only on unix env. Is there any way we can use ssis ? please advice if any other solution.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18449 Visits: 20430
Is there any way we can use ssis ?


To do what, precisely?

As you have given no examples of the data which is giving you problems, providing any sort of useful answer is close to impossible. How does SSIS fit into the architecture of your Unix environment?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sql crazy kid
sql crazy kid
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 243
Phil Parkin (1/12/2014)
Is there any way we can use ssis ?


To do what, precisely?

As you have given no examples of the data which is giving you problems, providing any sort of useful answer is close to impossible. How does SSIS fit into the architecture of your Unix environment?


Sample data looks like below, for 3rd row rowdelimiter is not correct.
|USD|1542.52|14536|xxxx
||1542.52|14536|
EUR|1542.52|14536|xxxx

I m not sure how does SSIS Fit into Unix, please advice me if SSIS fits into unix.

I can create an ksh scripts ,is ssis can be called from ksh ?
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18449 Visits: 20430
sql crazy kid (1/12/2014)
Phil Parkin (1/12/2014)
Is there any way we can use ssis ?


To do what, precisely?

As you have given no examples of the data which is giving you problems, providing any sort of useful answer is close to impossible. How does SSIS fit into the architecture of your Unix environment?


Sample data looks like below, for 3rd row rowdelimiter is not correct.
|USD|1542.52|14536|xxxx
||1542.52|14536|
EUR|1542.52|14536|xxxx

I m not sure how does SSIS Fit into Unix, please advice me if SSIS fits into unix.

I can create an ksh scripts ,is ssis can be called from ksh ?


Sorry but I know very little about Unix. But I'm pretty sure that SSIS can be used to process and push data into a Unix database, assuming the connectivity can be resolved.

But SSIS needs to be installed on a Windows server - is that a show-stopper for you? It's also quite expensive to justify if you are not already using SQL Server.

Does your sample data represent two or three rows of actual data?

If three rows, it looks like the column delimiters (not row delimiters) are incorrectly placed. That's just bad data and it should be fixed at source, not by you - in my opinion.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sql crazy kid
sql crazy kid
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 243
Phil Parkin (1/12/2014)
sql crazy kid (1/12/2014)
Phil Parkin (1/12/2014)
Is there any way we can use ssis ?


To do what, precisely?

As you have given no examples of the data which is giving you problems, providing any sort of useful answer is close to impossible. How does SSIS fit into the architecture of your Unix environment?


Sample data looks like below, for 3rd row rowdelimiter is not correct.
|USD|1542.52|14536|xxxx
||1542.52|14536|
EUR|1542.52|14536|xxxx

I m not sure how does SSIS Fit into Unix, please advice me if SSIS fits into unix.

I can create an ksh scripts ,is ssis can be called from ksh ?


Sorry but I know very little about Unix. But I'm pretty sure that SSIS can be used to process and push data into a Unix database, assuming the connectivity can be resolved.

But SSIS needs to be installed on a Windows server - is that a show-stopper for you? It's also quite expensive to justify if you are not already using SQL Server.

Does your sample data represent two or three rows of actual data?

If three rows, it looks like the column delimiters (not row delimiters) are incorrectly placed. That's just bad data and it should be fixed at source, not by you - in my opinion.



Here is few more details
Backend - SQL server 2008 R2,SSIS installed
OS - Unix Env

We have many process running on unix ,connecting to sql server , BCP into sql tables , all are working fine .

Above sample data is a representation of actual data . But really there is a correction required on data file , but in case we are using this in windows env this not an issue . Either with SSIS or Informatica this works perfectly with no issue as we have column to column mapping . Since that we cannot ask to fix that issue.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18449 Visits: 20430
Here is few more details
Backend - SQL server 2008 R2,SSIS installed
OS - Unix Env

We have many process running on unix ,connecting to sql server , BCP into sql tables , all are working fine .

Above sample data is a representation of actual data . But really there is a correction required on data file , but in case we are using this in windows env this not an issue . Either with SSIS or Informatica this works perfectly with no issue as we have column to column mapping . Since that we cannot ask to fix that issue.


OK. Then please answer my questions about your sample data. At the moment, it looks like:

c1 (blank),c2,c3,c4,c5
c1 (blank),c2 (blank),c3,c4,c5 (blank)
c1 (missing),c2,c3,c4,c5

My point? If columns can be missed out randomly from your source data, there is NO WAY ON EARTH to get it fixed by SSIS, which is lacking the necessary Paranormal Transformation.

If you are saying that only certain columns are missing, under certain circumstances, you need to explain the logic of what needs to be done please.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sql crazy kid
sql crazy kid
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 243
Phil Parkin (1/12/2014)
Here is few more details
Backend - SQL server 2008 R2,SSIS installed
OS - Unix Env

We have many process running on unix ,connecting to sql server , BCP into sql tables , all are working fine .

Above sample data is a representation of actual data . But really there is a correction required on data file , but in case we are using this in windows env this not an issue . Either with SSIS or Informatica this works perfectly with no issue as we have column to column mapping . Since that we cannot ask to fix that issue.


OK. Then please answer my questions about your sample data. At the moment, it looks like:

c1 (blank),c2,c3,c4,c5
c1 (blank),c2 (blank),c3,c4,c5 (blank)
c1 (missing),c2,c3,c4,c5

My point? If columns can be missed out randomly from your source data, there is NO WAY ON EARTH to get it fixed by SSIS, which is lacking the necessary Paranormal Transformation.

If you are saying that only certain columns are missing, under certain circumstances, you need to explain the logic of what needs to be done please.





Columns are not missing here . I have 5 column here ,looks like this.
[Just a sample data ,but this is the format we load]
col1 col2 col3 col4 col5
USD 1542.52 14536 xxxx
1542.52 14536
EUR 1542.52 14536 xxxx
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18449 Visits: 20430
Columns are not missing here . I have 5 column here ,looks like this.
[Just a sample data ,but this is the format we load]
col1 col2 col3 col4 col5
USD 1542.52 14536 xxxx
1542.52 14536
EUR 1542.52 14536 xxxx


What would this sample data look like if there were no problems with delimiters?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sql crazy kid
sql crazy kid
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 243
Phil Parkin (1/12/2014)
Columns are not missing here . I have 5 column here ,looks like this.
[Just a sample data ,but this is the format we load]
col1 col2 col3 col4 col5
USD 1542.52 14536 xxxx
1542.52 14536
EUR 1542.52 14536 xxxx


What would this sample data look like if there were no problems with delimiters?


I am getting output as like below when i do bcp command on unix.
C1 C2 C3 C4 C5
<NULL> USD 1542.52 14536 xxxx
<NULL> <NULL> 1542.52 14536 EUR|1542.52|14536|xxxx
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18449 Visits: 20430
sql crazy kid (1/12/2014)
Phil Parkin (1/12/2014)
Columns are not missing here . I have 5 column here ,looks like this.
[Just a sample data ,but this is the format we load]
col1 col2 col3 col4 col5
USD 1542.52 14536 xxxx
1542.52 14536
EUR 1542.52 14536 xxxx


What would this sample data look like if there were no problems with delimiters?


I am getting output as like below when i do bcp command on unix.
C1 C2 C3 C4 C5
USD 1542.52 14536 xxxx
1542.52 14536 EUR|1542.52|14536|xxxx


I understand, but that is not the question I am asking.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search