|
I just found out that opening the files in Notepad++ as codepage 1252 and saving them as UTF8, and then change the import from 1252 to UTF8, fixes the problem.
I'm putting this down as a bug from MS. (That probably won't ever be fixed)
The question is, what is the best way to fix it?
For a one off I will just open and save all the files and import them as UTF8.
But this will be a weekly import later on, preferably run as an SSIS package.
|
|
|
|
|
Have you tried using the "Import Flat File" wizard, instead of the "Import/Export Data" wizard? It's supposed to be better at this sort of thing...
Import Flat File to SQL - SQL Server | Microsoft Docs[^]
Otherwise, you might need to resort to Powershell or C#.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes, the "Import Flat File" wizard doesn't recognize the Text qualifier at all.
Meanwhile I have noticed that if I open a file in Notepad++ as codepage 1252 and saving it as UTF8, and then change the import from 1252 to UTF8, it works as advertised.
This is OK as a one off, but what is the best way to change the file encoding that can be run from inside an SSIS package?
|
|
|
|
|
Jörgen Andersson wrote: what is the best way to change the file encoding that can be run from inside an SSIS package?
You can add an "execute process" task to execute an external program to change the file encoding.
For example, you could use C# to change the encoding:
Converting a text file from one encoding to another - Buck Hodges[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes, this seems like the best option.
|
|
|
|
|
This might or might not help ... check what happens using Excel or Access and their X-referencing/inter-app-transferring abilities on specific data table (as .txt; I'm assuming you can morph tables by exporting them with whatever tool you're currently sleuthing the inputs and outputs).
In Excel or Access, generally speaking, parsing data using what Microsoft terms a "delimiter" and those various options of which you speak, can often run afoul of it's own preaching (doc code won't run as-printed) ... in which case deploying UDF using the copious functionality of VBA most times does the trick. A favorite workaround of mine is to use Split and InStr, along with Find and Search although logic there becomes draconian quickly.
It sounds like you're in the testing stages anyway.
[Pleaso oh please ... have Excel andor Access]
Final addition: Search Stack Overflow.
|
|
|
|
|
Excel doesn't handle text qualifiers as far as I can see. And handling stringsplits and such in Excel would indeed quickly become draconian.
Access I don't even want to try. 
|
|
|
|
|
Hello,
I have a table whose data is like this!
date=27/08/2021, Qte=11
03/09/2021, Qte=21
And I wanted to retrieve the value of 03/09/2021, Qte = 21 if the date of 29 is the date no entry is entered or no record exists, which query executed?
|
|
|
|
|
Are you saying you want the first record on or after the specified date?
SELECT TOP 1 [date], qte
FROM YourTable
WHERE [date] >= @SpecifiedDate
ORDER BY [date]; If that's not what you want, then you need to provided a clearer explanation of what you are trying to do.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i want to see the last record if the current date does not exist
|
|
|
|
|
SELECT TOP 1 [date], qte
FROM YourTable
ORDER BY [date];
|
|
|
|
|
How to access database using RDBMS from a object oriented language C++?
What are the ways to do it?
|
|
|
|
|
|
Hello,
How do you approach typical scenario of custom values (key/value pairs) for an entity? The example would be something like this:
class Person
{
string Name{get;set;}
IEnumerable<CustomProperty> CustomProperties {get;set;}
}
class CustomProperty
{
string Name{get;set;}
string Value{get;set;}
}
The problem is that Value needs to be of different types so e.g custom property called Color would have a string value of e.g. "Red" while Age would have to be treated as a number (even though it's stored as nvarchar). How would you approach this with a requirement to be able to filter and order over custom properties (multiple at once also with OR/AND) when querying Persons so I'd like to e.g. query over Persons of Age greater than 10 while Value is stored as nvarchar so casting is needed for comparing as numbers instead of strings.
The implementation is based on EF Core 5 and C#.
Possible ways:
1) Building query with casting/converting and adding PropertyType to the CustomProperty entity to decide which type is it. This would store everything in a single table CustomProperties. That's tricky but doable, I guess. The problem would be filtering and ordering with casting everything based on PropertyType. Might not be possible to create every queryable as translatable to TSQL.
2) Creating a separate table per a custom property type so creating tables: StringProperties, IntegerProperties, DecimalProperties which would simply be {string Name, int Number}, {string Name, string Value}, {string Name, DateTime Timestamp} etc. And all these would have a relationship to Person entity, lazy loaded. This would be kinda easy to implement, query and adding new types in the future.
3) The same as 2 but with latest EF Core 5 feature of Table Per Type, meaning there is a base class e.g. CustomProperty with Id and Name and specific classes deriving from base class e.g. IntegerProperty : CustomProperty. EF Core 5 creates the hierarchy in SQL correctly along with FKs. So pretty similar but using the latest feature.
|
|
|
|
|
|
I can't understand why would you like to be forced to create another table for relations if you can just use smth like references in most languages. What is the point of such complexity?
I would rather type:
companies.First(cmpn=>cmpn.Name="Netflix").Employees.Avg(empl=>empl.Sal)
then use relations:
select * from companies join company_employess on ... join ... group by.. avg
I found some NoSQL DBs like Couchbase and other alternatives but all the companies demand SQL-devs.
Why is it the way it is?
(I'm using ASP.NET 6 + Blazor)
|
|
|
|
|
Because you are not using the database you are using c# to do all your work. So only your application will work with the creation you call a database. Most of us (and I'm an old fart) know what a relational database is used for - storing data in a RELATED form and therefore any application can use the same relationships, reporting for instance.
You need to do some research into the difference between a document database (NoSQl) and a traditional RMBDS.
While there is a need and use for NoSql your example is not one of them EVERY company needs a RMDBS for this type of data.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hopeless Idealist wrote: I would rather type:...but all the companies demand SQL-devs.
First of course because companies do not operate to please you.
They operate to make money. And that comes from pleasing customers. Both in sales and in follow up support.
Additionally they must keep costs low.
At the end of the day technologies do not drive company success. Rather it is matter of successfully creating a product(s)/service(s) which the company can maintain and enhance. And of course then selling it in the first place.
Large companies almost always have multiple persisted datastores. And I only say 'almost' because I can't be sure that there is not one company out there that only uses one. I do know that Microsoft, Oracle, Netflix, Google and Amazon all use different ones.
|
|
|
|
|
hi ,
i have a list of id values ( listOfProducts ) returned from a dropdown list , i need to use them to filter in a sql request
something like this :
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (listOfProducts) (not sure about that)
i need to get all the records that have a product id that is contained inside the list from the dropdown
thanks
|
|
|
|
|
|
Send the list of products as comma seperated to parameter List of Products and change this query into Dynamic query.
For ex: @listOfProducts is parameter. Send as tilde or comma seperated to SQL query
Inside Query, split the comma seperated and change to list. Use the below script.
DECLARE @listOfProducts VARCHAR(2000)
SET @listOfProducts='174~175~78~77~191~399~451'
IF OBJECT_ID('tempdb..#listOfProducts') IS NOT NULL DROP TABLE #listOfProducts
SELECT
DISTINCT Split.a.value('.', 'VARCHAR(100)') AS Prod INTO #listOfProducts
FROM (SELECT
CAST ('<M>' + REPLACE(@listOfProducts, '~', '</M><M>') + '</M>' AS XML) AS String )
AS A CROSS APPLY String.nodes ('/M') AS Split(a)
WHERE Split.a.value('.', 'VARCHAR(100)')<>''
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (select Prod from #listOfProducts)
|
|
|
|
|
Just for the record, that is not a Dynamic Query
|
|
|
|
|
There's no table named "S_ware" ... so the FROM clause will issue (most likely) "Invalid object name". I've also noticed that, in the second post to this thread, you've given a bit more information ... but you're declaring a variable as VARCHAR; the SET is ok. But suddenly 'listOfProducts' has become a temporary TABLE. So there are naming issues also.
This all sounds like pedantry from me so I'll quit. Suffice to say I'd look up TABLE, SET, DECLARE, and the advanced XML in the BOL for SQL Server version you think you have.
|
|
|
|
|
I need to store lots and lots and lots of timeseries data. Each series is keyed by a unique key, and once written it will almost never be updated. I'm literally after a key/value storage system that's persisted.
I also don't want to use Azure. No offense, Microsoft, but...
So my thoughts were Redis (not the safest), postgres (bit overkill), Cassandra (seems to like writes better than reads and my use case is the opposite), or mongoDB.
Did I mention I want this to cost less than a coffee a week. A good coffee, but I'm not paying $50 a month for this. Total data will be < 1TB.
Most likely this will be running on a Windows or Linux box against a .NET 5 app.
cheers
Chris Maunder
|
|
|
|
|
Chris Maunder wrote: and once written it will almost never be updated
I love specs like this - NOT.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
