excel to csv correct format for sage

  moorie 18:30 17 May 06
Locked

hi i have a spreadsheet with all items and correct prices i wish to import to sage instant accounts however the format needs to be csv,i can save as csv but still there is criteria that cannot be imported ie spaces " , / is there a way of editing columns to remove the above so import will be seamless
regs
Darren

  harristweed 18:48 17 May 06

have you tried "find and replace" with replace left blank?

  moorie 19:36 17 May 06

YEAH DIDNT WORK

  Jamb0 20:08 17 May 06

I think you actually have to type a space in the top field (find what) but leave the replace field blank.

  VoG II 21:15 17 May 06

This is not at all clear, moorie. What exactly are you trying to do.

And shouting 'YEAH DIDNT WORK' will help nobody, let alone you.

  moorie 07:28 18 May 06

hi sorry wasnt shouting accidently knocked caps lock on and posted reply without realising
what im trying to do is import a pricelist into sage instant accounts.it has to be in csv format to accept it,also it will not accept spaces in code descriptions or symbols such as ",%, etc.otherwise import will be unsuccessful.when saving the excel spreadsheet in csv format it retains the symbols.what i was wondering is there a way of removing the symbols from the spreadsheet rather than manually correcting each error. there are over 4000 symbol references in the spreadsheet.
hi hope this is clear?

  VoG II 18:15 18 May 06

By default a .csv (Comma Separated Value) file will contain commas (,).

You will get quotation marks in text files even when you save manually if entry in a single cell has list separator (generally comma). You can avoid quotation marks while saving manually, by changing the list separator in your computer (Control Panel | Regional Settings | Number | List Separator) to some character other than the character you have in your cell content.

Visual Basic (unlike Excel itself) doesn't take often the regional settings in account. It will always save CSV files with the delimiter used in the US (comma). Although disk I/O with text files is very slow in Visual Basic, you should use an appropriate macro. An example (taken from elsewhere) is below.


Function Exporte(Wksht As Worksheet, NomFic As String, _
Optional Remplace As Boolean = True) As Long

Dim UsedRange As Range
Dim NbCols As Integer, NbLignes As Long
Dim Stat As Integer, Incr As Integer
Dim I As Integer, J As Integer
Dim Progr As Integer

On Error GoTo Erreur
If Dir(NomFic) < "" And Not Remplace Then

Exporte = -1
Exit Function
End If
Open NomFic For Output As #1
Set UsedRange = Wksht.UsedRange
NbCols = UsedRange.Columns.Count - 1
NbLignes = UsedRange.Rows.Count
Stat = NbLignes / 40
Incr = Stat
For I = 1 To NbLignes
If I = Stat Then
Stat = Stat + Incr
Progr = Progr + 1
Application.StatusBar = _

"Exportation " & String(Progr, ".")
End If
For J = 1 To NbCols
Print #1, Cstr(UsedRange(I, J)) & ";";
Next J
Print #1, Cstr(UsedRange(I, J))
Next I
Close 1
GoTo Fin
Erreur:
Exporte = Err
Fin:
Application.StatusBar = False
End Function

Sub Test()
Dim Result As Long

Result = Exporte(ActiveSheet, "Test.csv", False)
Select Case Result
Case -1
MsgBox "File does Not exist", vbExclamation
Case 0
MsgBox "File exported."
Case Else
MsgBox Error(Result)
End Select
End Sub

This VBA procedure will save the active worksheet as tab delimited (text):


Sub SaveAsText()
ActiveWorkbook.SaveAs FileName:="C:\My Documents\MyFile.txt", FileFormat:=xlText
End Sub

  VoG II 18:16 18 May 06

If none of this helps, post your question at MrExcel.com

This thread is now locked and can not be replied to.

LG G6 review: Hands-on with LG’s bold, big-screen shot at perfection

1995-2015: How technology has changed the world in 20 years

How the 2017 Oscar-winning VFX of The Jungle Book were created

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…