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
avirAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kulboyCommented:
Why dont you try it the other way?
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
avirAuthor Commented:
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.
kulboyCommented:
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?)
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

avirAuthor Commented:
I don't know if it is allowed. I'll have to check. Thanks.
kulboyCommented:
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
avirAuthor Commented:
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.
avirAuthor Commented:
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.
Ejgil HedegaardCommented:
The text delimiter is |, but there are several line feed characters (char code 10) in the text, creating this behaviour.
It looks as every line (except the header) should end with the picture specification, end with .jpg.
Try this macro which will replace all LF with space.

Option Explicit

Sub RemoveLF()
    Dim File1 As String, File2 As String, Text As String, Text2 As String, Pos As Long
    Dim ws As Worksheet, rw As Long
    Set ws = Worksheets("Sheet1")
    Application.ScreenUpdating = False
    File1 = ThisWorkbook.Path & "\blue.csv"
    File2 = ThisWorkbook.Path & "\blue1.csv"
    Open File1 For Input As #1
    Open File2 For Output As #2
    Line Input #1, Text
    Close #1
    rw = 1
    Text = Replace(Text, Chr(10), " ")
    Pos = InStr(1, Text, "TypeE", vbTextCompare) + 5
    Text2 = Left(Text, Pos)
    If Right(Text2, 1) = " " Then
        Text2 = Left(Text2, Len(Text2) - 1)
    End If
    ws.Range("A" & rw) = Text2
    Print #2, Text2
    Text = Right(Text, Len(Text) - Pos)
    rw = 2
    Do
        Pos = InStr(1, Text, ".jpg", vbTextCompare) + 4
        If Pos > 0 Then
            Text2 = Left(Text, Pos)
            If Right(Text2, 1) = " " Then
                Text2 = Left(Text2, Len(Text2) - 1)
            End If
            ws.Range("A" & rw) = Text2
            Print #2, Text2
            If Pos < Len(Text) Then
                Text = Right(Text, Len(Text) - Pos)
            Else
                Pos = 0
            End If
        End If
        rw = rw + 1
    Loop While Pos > 0
    Close #2
End Sub

Open in new window



Put the Excel file in the same folder as the file blue.csv, and the macro will make a new file blue1.csv.
The lines are placed in Sheet1, and the text import (blue1.csv) in Sheet2 in Hebrew.
The result looks ok to me with Item number in column A, and picture name incolumn J.
See files.
RemoveLF-in-textfile.xlsm
blue1.csv

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kulboyCommented:
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.
avirAuthor Commented:
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.
Ejgil HedegaardCommented:
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.
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 Sub

Open in new window

Result of import and restore in Sheet2
RemoveLF-in-textfile-v2.xlsm
blue2.csv
avirAuthor Commented:
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.
Ejgil HedegaardCommented:
Perhaps your file definition for xlsm files are missing in Windows.
Save the file RemoveLF-in-textfile-v2.xlsm in the same folder as the blue.csv file.
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.
avirAuthor Commented:
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.
Martin LissOlder than dirtCommented:
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.
avirAuthor Commented:
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.
Martin LissOlder than dirtCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.