Create columns via length - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create columns via length

Author

Topic

ocean
Starting Member

22 Posts

Posted - 2012-05-01 : 11:47:06

Hi there,

I have a file which contains alphanumeric info and I need to create different columns with that info. In order to classify it.

The problem is whenever I import the info, SQL process it in one column, because It doesn't have comas in order to differentiate , each different value

e.g

Peter 10 C10 12/01/2012

I need a column for the name, size, locker and date.

But SQL just take everything as one column and I cannot work with the data like that, I have tones of info.

Does anyone have any clue about that?

I did try:

Select replace (Column, ' ', '') from Mydatabase

I know I have as result: Peter10C1012/01/2012. The other way is to count the characters and take the info that way.

e.g

Peter = 5 characters if SQL counts. After counting 5, create a new column with "name"

IIm dying to get an answer, Thanks!!!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-01 : 11:53:12

There's a couple ways to do this. This will work on your example set, but only if there's on;y and always 3 spaces.
declare @str varchar(30) = 'Peter 10 C10 12/01/2012'

set @str = REPLACE(@str,' ','.')

select PARSENAME(@str,1)
,PARSENAME(@str,2)
,PARSENAME(@str,3)
,PARSENAME(@str,4)

A better way is to search this site for fnParseValues that will take you data and turn it into a table with each value in your string becoming it's own row.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 11:54:33

if the data is separated by space why dont you set column delimiter as space? how are you importing the file data to sql server? using export import wizard or programatically?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ocean
Starting Member

22 Posts

Posted - 2012-05-01 : 12:08:11

Hi visakh16,

Im using the export wizard, I tried the column delimiter, but only allow me to use the regular delimiters:

{CR}/{LF} or both at the same time
semicolon {;}
colon {:}
Comma{,}
tab{t}
vertical bar{|}

I don't know who to use the space as a limiter... If I can do it, for sure is going to be the fastest way. Which steps do I have to follow? Appreciate your prompt response!

Hi jimf

I appreciate your response, but I don't have only 3 spaces, depends on each different case

Any idea?

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:12:05

quote:
Originally posted by ocean

Hi visakh16,

Im using the export wizard, I tried the column delimiter, but only allow me to use the regular delimiters:

{CR}/{LF} or both at the same time
semicolon {;}
colon {:}
Comma{,}
tab{t}
vertical bar{|}

I don't know who to use the space as a limiter... If I can do it, for sure is going to be the fastest way. Which steps do I have to follow? Appreciate your prompt response!

Hi jimf

I appreciate your response, but I don't have only 3 spaces, depends on each different case

Any idea?

Thanks a lot

did you try typing a space in delimiter column and check the data whether it got split up correctly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:14:48

alternate methods are

if you're sure of start positions of columns in file, you can use fixed length format rather than delimited in connection manager.
Another way is to use BULK INSERT to get data from file and use format file to specify the format of data with correct delimiters

see

http://msdn.microsoft.com/en-us/library/ms178129.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-01 : 12:16:55

quote:
Originally posted by ocean

Hi there,

I have a file which contains alphanumeric info and I need to create different columns with that info. In order to classify it.

The problem is whenever I import the info, SQL process it in one column, because It doesn't have comas in order to differentiate , each different value

e.g

Peter 10 C10 12/01/2012

I need a column for the name, size, locker and date.

But SQL just take everything as one column and I cannot work with the data like that, I have tones of info.

Does anyone have any clue about that?

I did try:

Select replace (Column, ' ', '') from Mydatabase

I know I have as result: Peter10C1012/01/2012. The other way is to count the characters and take the info that way.

e.g

Peter = 5 characters if SQL counts. After counting 5, create a new column with "name"

IIm dying to get an answer, Thanks!!!!




Try to Load your file in your table

bulk insert databasename..youtable   from 'C:\Data\yourfile.txt'
with (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = '',

    ROWTERMINATOR = '\n'

  );

ocean
Starting Member

22 Posts

Posted - 2012-05-01 : 12:31:20

quote:
Originally posted by visakh16

alternate methods are

if you're sure of start positions of columns in file, you can use fixed length format rather than delimited in connection manager.
Another way is to use BULK INSERT to get data from file and use format file to specify the format of data with correct delimiters

see

http://msdn.microsoft.com/en-us/library/ms178129.aspx

------------------------------------------------------------------------------------------------------
The matter is that the length is not always the same in each "column" due to the numbers of the alphanumeric data...

So I want to take the MAX length of each column and after that, work with my columns. And see who much info i'm losing because of the delimiter.

Or, import the data using the wizard delimiter, but still, i'm not a pro of SQL and I don't know how can I use a space delimiter for importing, I only can see the delimiters that I wrote you in the last post..

Thanks visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:32:38

Did you refer link to see how you define delimiters with BULK INSERT?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ocean
Starting Member

22 Posts

Posted - 2012-05-01 : 12:32:44

quote:
Originally posted by ocean

quote:
Originally posted by visakh16

alternate methods are

if you're sure of start positions of columns in file, you can use fixed length format rather than delimited in connection manager.
Another way is to use BULK INSERT to get data from file and use format file to specify the format of data with correct delimiters

see

http://msdn.microsoft.com/en-us/library/ms178129.aspx

------------------------------------------------------------------------------------------------------
The matter is that the length is not always the same in each "column" due to the numbers of the alphanumeric data...

So I want to take the MAX length of each character and after that, work with my columns. And see who much info i'm losing because of the delimiter.

Or, import the data using the wizard delimiter, but still, i'm not a pro of SQL and I don't know how can I use a space delimiter for importing, I only can see the delimiters that I wrote you in the last post..

Thanks visakh16

ocean
Starting Member

22 Posts

Posted - 2012-05-01 : 12:38:26

[quote]Originally posted by visakh16

Did you refer link to see how you define delimiters with BULK INSERT?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Let's see if works.

Thanks visakh16!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 14:17:22

welcome
lemme know if you need more help on BULK INSERT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ocean
Starting Member

22 Posts

Posted - 2012-05-02 : 06:00:36

[quote]Originally posted by visakh16

welcome
lemme know if you need more help on BULK INSERT

------------------------------------------------------------------------------------------------------

Good morning visakh16,

So let's say I have the following info in .txt

M 12 C1 Peter AUS 100 2 G5 Fischer Up 120411 120711 AL 121211 UP

Using the last example, I need different 15 columns for my raw data. (dbo.RD_C)

I did the Table required in the "Bulk Insert Process". But after that I don't know how to continue with the queries.

I tried to use the query:

Bulk insert dbo.RD_C from 'c:\test.txt'

with (field terminator = ? >>>> i Don't have in my case field terminator, and if I do I could use in that case SSIS import tool..

rowterminator = '\n )

I can use the Command prompt, but in that case, I dont know how to specify my data file..

Any idea?

Many Thanks

ocean
Starting Member

22 Posts

Posted - 2012-05-02 : 06:14:12

quote:
Originally posted by vijays3

[quote]Originally posted by ocean

Hi there,

I have a file which contains alphanumeric info and I need to create different columns with that info. In order to classify it.

The problem is whenever I import the info, SQL process it in one column, because It doesn't have comas in order to differentiate , each different value

e.g

Peter 10 C10 12/01/2012

I need a column for the name, size, locker and date.

But SQL just take everything as one column and I cannot work with the data like that, I have tones of info.

Does anyone have any clue about that?

I did try:

Select replace (Column, ' ', '') from Mydatabase

I know I have as result: Peter10C1012/01/2012. The other way is to count the characters and take the info that way.

e.g

Peter = 5 characters if SQL counts. After counting 5, create a new column with "name"

IIm dying to get an answer, Thanks!!!!

[code]

Try to Load your file in your table

bulk insert databasename..youtable from 'C:\Data\yourfile.txt'
with (

DATAFILETYPE = 'char',

FIELDTERMINATOR = '',

ROWTERMINATOR = '\n'

);
====================================================

Hi vijays3,

I can solve the filedterminator, but whenever im executing the query I got:

Msg 4861, Level 16, State 1, Line 1 ""the system cannot find the path specified''

I changed the path several times, in order to have a direct access to the file, but even doing some copies in my C:\ I still have the same issue.

Any idea?

Many thanks

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-02 : 11:18:27

[code]
Replace single quotes to double quotes in :

bulk insert databasename..youtable from "C:\Data\yourfile.txt"
with (

DATAFILETYPE = 'char',

FIELDTERMINATOR = '',

ROWTERMINATOR = '\n'

);[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-02 : 19:33:42

in case of specifying custom row and column delimiters you can also use format file inside BULK INSERT

http://msdn.microsoft.com/en-us/library/ms178129.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources