| Author |
Topic |
|
tinamiller1
Yak Posting Veteran
78 Posts |
 Posted - 2012-01-05 : 13:34:04
|
| SO if I have -q" does that mean it will take that .csv file and disregard any text that has " " around it as well as import columns with null values that have the " " around it?so far as qualifiers I have my -c -t, -r \n -T but it gives an error of the .csv having an EOF issue. I opened it in UltraEdit since it is 12.7 millions rows with 50 columns and the only thing I see are null values between " " and " " around all fields including the column names. Oh 1 more question. I need to put a -F because SQL Server has a script I run to create my table and column headers with datatypes and widths before hand. All I am doing is importing the data but need to have it not look at the first row. I assume that is what the -F is for but noticed in the instructions you put a number there so would I have my qualifiers like this:-c -t, -q" -r \n -F50 -Ttina m miller |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-01-05 : 13:37:18
|
Different meaning (although you would THINK M$ would have accomodated for this)Data is Data, Quotes or no Quotes."This is Data","This too, is Data"Is an EXCEL abominationBOLquote:
-q Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and an instance of SQL Server. Use this option to specify a database, owner, table, or view name that contains a space or a single quotation mark. Enclose the entire three-part table or view name in quotation marks ("").
Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-05 : 14:03:58
|
| it is a .csv file that has been zipped and I unzipped it and they have all 50 columns formatted as"name", "name", "name", etc of course all column names are different like "employeeID"then the data beings on row 2 and some of the fields are blank. like there might be column "ProviderType", with nothing in it because that field was not filled out. Wished I could be a bit more specific but HIPAA.the bcp command is:bcp databasename.dbo.tablename in "c:\documents and settings\tmill29\my documents\uhnanalytics\oxford\claims2009q2.csv" -SServerName -c -t, -r \n -Twith that entry I get unable to open BCP host data -fileNow I have tested bcp on just making up a test.csv and a test table in the server named test and put in lastname firstname birthdate as column namesthen i put data in each column but i began it on row 1 so i am only importing data and not the column headers. wonder if that is my issue. If i do not use the -q then what do I put to say disregard row 1 cuz that is the columns which are already in the file.tina m miller |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-05 : 14:06:21
|
| by the way I have read all the bcp items on msdn that have definitions for -c -t etc etc but some just make zero sensetina m miller |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-05 : 14:10:26
|
| i think i will try the import/export function in sql server as I did with q1 2009 claims from oxford and it will give me import errors and actually tell me the rows that are erroring. only downside is there are 12+ million rows and the last time i used this function it errored at 284k row found the problem fixed and did it again. then errored at row 902k found the error and fixed it. I continued down this path til all errors in that file were fixed and it finally transferred successfully. but the whole process for just 1 quarter worth of data with import, corrections reimport took me 17 hours. That is just plain dumb and we are trying to get the Oxford group to clean up the file so all we have to do is run the script to create the table then do bcp to import the data. Trying to see if import with SQL server is faster once we know it is successful or the bcp is faster. Once I have a clean file the entire 12+ million rows imported using SQL Server in about 7.5 hours.tina m miller |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-05 : 14:17:51
|
| If I understand the -F I think I put that in as -F2 to tell it to begin reading from row 2?tina m miller |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-05 : 14:24:19
|
| since it is 12 mill plus rows think I am going to make a copy of the .csv and take the first 1000 and do some testing of different items at the end instead of trying to test on this huge filetina m miller |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-05 : 15:04:10
|
| The only thing I can see when I open the .csv with ultraedit is now they have " " around everything. So odd that using SQL Server import function and I say text is " " it imports and is already to 805k with no errors. But boss wants to see how bcp does and if it is faster than this import so I have to figure this out so I can give her the commands in an instructional document and the time in bcp versus time just on importing. This is why I think it has something to do with the " " around the text that the bcp is not liking because if SQL import is working with me saying hey there is " " around all the texttina m miller |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-01-05 : 15:12:37
|
quote:
Originally posted by tinamiller1bcp databasename.dbo.tablename in "c:\documents and settings\tmill29\my documents\uhnanalytics\oxford\claims2009q2.csv" -SServerName -c -t, -r \n -Twith that entry I get unable to open BCP host data -file
Where is the file located? On your machine, a file server or SQL Server?Wgere are you running the command from?Your machine or the server?What credentials are you using?What sql server security model are you using?Are you using xp_cmdshell?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-05 : 15:34:34
|
| It is on local machine.I am running from the DOS window by typing in cmd and getting into DOSNot sure about the credential question.I have MS 2008 not sure on xp_cmdshell. I do not type that in first but if typing in cmd in run and it takes to DOS then yes.The big issue is I tested this already by creating a fake file of course not the size of this one from Oxford. I had 10 rows in mine and the bcp command worked.I think it is something with this file itself that I might have to clarify in my bcp command. The file that was successful when I did my test was this:bcp database.dbo.tablename in c:\documents and settings\tmill29\my documents\uhnanalytics\oxford\test.csv" -Sservername -c -t, \n -TIt was successful. Now my file might not have been formatted crazy like the oxford is. Do not know cuz ultraedit is taking forever to let me select just the top 1000 rows out of the 12milliontina m miller |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-05 : 15:38:17
|
| I guess also what is bothering me about this whole bcp is that right now the import function in SQL Server that allowed me to say tab delimeted and the fields have " " around them and select the size of the field is up to a successful transfer of 1.3 million rows so far. So it is just something that is awry with my entry in bcp. I used the same as I did for my test file that was successful. how oddtina m miller |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-06 : 10:23:39
|
| on a bcp import how to i tell the system not to include the first 1 which is the column names?tina m miller |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-06 : 10:45:40
|
| Ok. I figured out it I put -F2 in it that it will start at row 2. The only issue now is with my test of 1000 rows it has an odd output. There are 50 total columns and using bcp it placed things like this:userID lastname firstname providername admtdt dischargedt"OXHP" "Jones" "Sarah" " " " " "19jun00","23jun00"I do have -t, in my bcp entry. I was getting EOF error. The only thing I can think of is that even though there are only 50 columns the data has more than 50 columns. I think those that produced the file have messed something up. I am thinking maybe I will use ultraedit to remove the "" and see what effect that has. I wished I could find a way in the bcp line to put something in to tell it to remove the "".tina m miller |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-06 : 11:54:36
|
| Well then on bcp import it loads it like this:userID lastName firstName providername admtdt dischargedt"userid" "lastname" "firstname" "providername" " " "admtdt","dischargedt""OXHP" "Jones" "Sarah" " " " " "19jun00","23jun00"tina m miller |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-06 : 12:27:27
|
| i found 1 thing that might be causing the EOF error. The data file I am given is not consistent. They have "" around text and have it "OXHP", "Lastname" with a space between the " ending the OXHP and beginning of the next " but in some areas of the row I found they do not have a space. It is like this "admitdt","dischargedt" so it is messing up the bcp. i need to find a way to have it disregard spacing i guesstina m miller |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-01-06 : 13:46:56
|
| The reason this is not working right for me is the .csv file that is 12 million plus rows is formatted in various ways and that is why it does not work. The guys in India will not format the file so I can do a bcp because they do not have the time. So I am stuck with I guess just doing this as a normal import/export function. Not sure why since that I say it is , and then " around text and it imports completely fine. No extra steps needed. So what secret does import/export function in SQL Server have that bcp does not?tina m miller |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-01-06 : 15:08:22
|
| It's just a different "program"bcp was written a loooooong time ago...and even BULK IMPORT works just kike bcpMeaning..they didn't have to bend to the "Standards" of data wrapped in double quotes. BUT Data is Data...a double quote is data...so bcp, in my mind (like ALL other platform data loaders) is in it's sense in the purest form....IF, they ever ask you in the futureNo Double Quotes, Delimiter is to be |~|Everything will work with thatBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
Next Page
|
BCP