Reading Large dataset in excel

[navb]

I want to read a large dataset from excel. Currently my excel has 50,000 rows and 10,000 columns. I want to export it to pipe delimited text file. I tried saving te file into csv format but was not able to convert it to pipe delimited text file. I then changed the region and language setting to | . This time if I closed the csv after saving, and then changed the extension of .csv to .txt, file was still loading as comma separator. But when I manually closed the file, it was asking whether I want to save the format. If I clicked yes then I was getting the desired result. Can someone help me to save the fileformat without getting the prompt and then change the .csv extension to .txt ?
Here is the code I got so far :

ChangeSettingExample is used to change the region and language settings.
ExportWorksheetWithCustomDelimiter saves the excel file as csv



Private Declare Function SetLocaleInfo _
Lib "kernel32" Alias "SetLocaleInfoA" ( _
ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String) As Boolean


Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()


'Private Const LOCALE_SDECIMAL = &HE
Private Const LOCALE_SLIST = &HC




Private Sub ChangeSettingExample(strSeparator As String)
'change the setting of the character displayed as the decimal separator.
'Call SetLocalSetting(LOCALE_SDECIMAL, ",") 'to change to ","
Call SetLocalSetting(LOCALE_SLIST, strSeparator) 'to change to ","
'check your control panel to verify or use the
'GetLocaleInfo API function
'Stop
'Call SetLocalSetting(LOCALE_SDECIMAL, ".") 'to change to ","
'Call SetLocalSetting(LOCALE_SLIST, ",") 'to back change to "."
End Sub


Private Function SetLocalSetting(LC_CONST As Long, Setting As String) As Boolean
Call SetLocaleInfo(GetUserDefaultLCID(), LC_CONST, Setting)
End Function




Public Sub ExportWorksheetWithCustomDelimiter()
Dim DisplayAlerts As Boolean
Dim FileNumber As Long
Dim FileData As String

Dim wkb As Workbook
Dim wks As Worksheet

On Error Resume Next
Call ChangeSettingExample("|")

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & "Export_Text - To Convert.xlsm")
Set wks = wkb.Worksheets(1)

FilePath = ThisWorkbook.Path & "\" & "TestFile.csv"
wkb.SaveAs FileName:=FilePath, FileFormat:=xlCSV, CreateBackup:=False

wkb.Close SaveChanges:=False
End Sub