avir
asked on
mssql to csv not formatting correctly
I have number of sql 2008 tables, some in English and some in Hebrew and some in both languages that I am trying to export to a spreadsheet (excel or google drive) and cannot seem to get them to display correctly. I am using the sql server import export tool. When I tried to export to excel directly, the Hebrew characters come out as question marks. When I try to export to a csv file using the unicode option and import that into excel, the Hebrew looks okay but many of the rows break into two or three rows. I can't figure out what the problem is. I've used different delimiters, but that doesn't seem to make a difference. Many of the rows have html tags and the columns are of varying lengths, some with large amounts of texts. I don't know if there are any limits with csv.
I've attached an example file and even without understanding Hebrew one can see where the rows are breaking in the middle of the item. Each item starts with an item number.
I would appreciate any insights.
blue.csv
I've attached an example file and even without understanding Hebrew one can see where the rows are breaking in the middle of the item. Each item starts with an item number.
I would appreciate any insights.
blue.csv
ASKER
Since I work on the server over a remote connection, I think this would mean installing excel on the server which would create all kinds of bureaucratic and technical problems having to include the IT department, which is something I am reluctant to do.
you can remotely connect to the sqlserver. You dont need to be on the machine.
just set the connection to the sqlserver instance. (if that ia allowed?)
just set the connection to the sqlserver instance. (if that ia allowed?)
ASKER
I don't know if it is allowed. I'll have to check. Thanks.
you can do a quick check by testing an odbc connector:
check here some easy steps:
http://help.bluemangolearning.com/m/screensteps-workgroup/l/17300-creating-odbc-connections-on-windows
check here some easy steps:
http://help.bluemangolearning.com/m/screensteps-workgroup/l/17300-creating-odbc-connections-on-windows
ASKER
I can't seem to connect to the remote sql server. I've done all the configurations and all the enabling, but I think I'm missing correct way to connect to the sql specific instance. If I just put in the name of the sql server, which I did, how does my computer know to connect to the remote server. And if I enter the remote ip/sql server, it still doesn't find the server.
This is the error I get when I try to add an ODBC connection:
Attempting connection
[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53].
[Microsoft][SQL Server Native Client 11.0]Login timeout expired
[Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
This is the error I get when I try to add an ODBC connection:
Attempting connection
[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53].
[Microsoft][SQL Server Native Client 11.0]Login timeout expired
[Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
ASKER
Anyways, right now I'm following this up out of curiosity.
Regarding my particular problem with the csv files, because I was under a certain amount of pressure with this, I exported the csv files I need right now and went over them closing rows and white spaces. It was very time-consuming and tedious, but it will do for now.
Regarding my particular problem with the csv files, because I was under a certain amount of pressure with this, I exported the csv files I need right now and went over them closing rows and white spaces. It was very time-consuming and tedious, but it will do for now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
probably the sqlserver is not configured to receave connections from your subnet, (you might check with a ping to that machine) or it doesnt allow that type of connection from your enviroment.
ASKER
Hi kulboy I think I'm already out of my league going in this direction. I might have to enlist some help from the IT department if I want to continue with your solution.
Hi Ejgil Hedegaard thanks for your idea. When you write that there are "several line feed characters (char code 10) in the text" do you mean physical line breaks or html tags like <br> or <p>? Also this is just an example of a particular table. I have about 10 tables and each one is different. Anyways, I will try your solution when I get back to the office on Thursday.
Hi Ejgil Hedegaard thanks for your idea. When you write that there are "several line feed characters (char code 10) in the text" do you mean physical line breaks or html tags like <br> or <p>? Also this is just an example of a particular table. I have about 10 tables and each one is different. Anyways, I will try your solution when I get back to the office on Thursday.
When you write a text in an Excel cell, you can force a line break in the cell using Alt+Enter.
That inserts character code 10 (LF).
But character code 10 is also used in the text file to make a new line, starting the next record.
So a LF in the middle of the record, push the import down to the next line, importing to "ItemNumE".
Perhaps it is intended to have line breaks in the cells, so here is a new file creating blue2.csv, where LF is not replaced with space, but with character 127.
Then import blue2.csv, using delimiter |, and run this code to restore the in cell line breaks.
The code is in Module2.
RemoveLF-in-textfile-v2.xlsm
blue2.csv
That inserts character code 10 (LF).
But character code 10 is also used in the text file to make a new line, starting the next record.
So a LF in the middle of the record, push the import down to the next line, importing to "ItemNumE".
Perhaps it is intended to have line breaks in the cells, so here is a new file creating blue2.csv, where LF is not replaced with space, but with character 127.
Then import blue2.csv, using delimiter |, and run this code to restore the in cell line breaks.
The code is in Module2.
Sub InsertBreak()
Dim cl As Range
Application.ScreenUpdating = False
For Each cl In ActiveSheet.UsedRange
cl.Value = Replace(cl.Value, Chr(127), Chr(10))
Next cl
End SubRemoveLF-in-textfile-v2.xlsm
blue2.csv
ASKER
Hi. I can't open the macro files. I tried copying the code above and creating a macro, but I get a subscript error when I try to run it. Needless to say I'm not very good at this.
Perhaps your file definition for xlsm files are missing in Windows.
Save the file RemoveLF-in-textfile-v2.xl
Right click the files here and select "Save link as"
Open Excel, and then open the macro file from Excel.
If you get a warning just above the sheet, open that, and accept macros.
Sheet1 shows how the file is saved, and I used it to see that it looked useful.
Sheet2 is the import of blue2.csv in Hebrew, with line breaks restored in the cells.
The macro RemoveLF creates the file blue2.csv in the same folder as the Excel file.
The other macro InsertBreaks run on the active sheet, and is intended to use after import, to insert the in-cell line breaks again.
Subscript errors depend on where you are in the code, and without knowing that, it does not tell much.
Save the file RemoveLF-in-textfile-v2.xl
Right click the files here and select "Save link as"
Open Excel, and then open the macro file from Excel.
If you get a warning just above the sheet, open that, and accept macros.
Sheet1 shows how the file is saved, and I used it to see that it looked useful.
Sheet2 is the import of blue2.csv in Hebrew, with line breaks restored in the cells.
The macro RemoveLF creates the file blue2.csv in the same folder as the Excel file.
The other macro InsertBreaks run on the active sheet, and is intended to use after import, to insert the in-cell line breaks again.
Subscript errors depend on where you are in the code, and without knowing that, it does not tell much.
ASKER
It looks pretty clear, unfortunately I will have to wait until I return to the office after our holidays in another week, to try it out. Thanks.
I've requested that this question be closed as follows:
Accepted answer: 250 points for kulboy's comment #a40985950
Assisted answer: 250 points for hgholt's comment #a40987062
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 250 points for kulboy's comment #a40985950
Assisted answer: 250 points for hgholt's comment #a40987062
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
Hi
I haven't had a chance to try and implement the solution so I would like to keep the question open a little bit longer.
Thanks.
I haven't had a chance to try and implement the solution so I would like to keep the question open a little bit longer.
Thanks.
I've requested that this question be closed as follows:
Accepted answer: 250 points for kulboy's comment #a40985950
Assisted answer: 250 points for hgholt's comment #a40987062
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 250 points for kulboy's comment #a40985950
Assisted answer: 250 points for hgholt's comment #a40987062
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Import the data in excell (let him do the hard part) and then save too csv?
https://support.office.com/en-US/Article/Connect-to-import-SQL-Server-data-0f3d2e8f-7d94-4ded-baa6-3b0df183ff98